Introduction to the use of MySQL splicing functions:
There are three common splicing functions in MySQL
concat concat_ws group_concat
Following is a brief introduction to concat_ws and concat of MySQL splicing function.
Specific usage can be logged on to MySQL shell to help concat; concat concat_ws; concat_ws group_concat to see help
1. Introduction of CONCAT() function:
1.1 CONCAT () Function Introduction:
The CONCAT () function is used to connect multiple strings into a single string, but it cannot specify characters as stitching symbols for stitching strings.
Grammar and usage characteristics:
CONCAT(str1,str2,…)
Returns a string whose result is a connection parameter. If any parameter is NULL, the return value is NULL. You can have one or more parameters.
Official examples:
Examples:
mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'root@localhost [test02]>SELECT concat('-',1,22,'Hello') display_name;
+--------------+
| display_name |
+--------------+
| -122 Hello |
+--------------+
1 row in set (0.00 sec)When using concat function, one of the characters spliced is NULL, and the whole string spliced becomes NULL.
root@localhost [test02]>SELECT concat('-',1,22,'Hello',NULL) display_name;
+--------------+
| display_name |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
root@localhost [test02]>SELECT concat('-',1,'Hello',NULL,22) display_name;
+--------------+
| display_name |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)1.2 Introduction to the Simple Use of Conat Function:
Let all table data in MySQL test01 library migrate to test02 Library
root@localhost [information_schema]>select concat("rename table test01.", table_name," to test02.",table_name,";") from tables where table_schema='test01' into outfile '/tmp/1.sql';
Query OK, 2 rows affected (0.00 sec)
[root@VM_82_178_centos tmp]# cat /tmp/1.sql rename table test01.student3 to test02.student3; rename table test01.test1_event to test02.test1_event;
root@localhost [information_schema]>source /tmp/1.sql Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.05 sec) root@localhost [test02]>show tables; +------------------+ | Tables_in_test02 | +------------------+ | student3 | | test1_event | +------------------+ 2 rows in set (0.01 sec) root@localhost [test02]>use test01; Database changed root@localhost [test01]>show tables; Empty set (0.00 sec)
2. Introduction of CONCAT_WS() function:
CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first parameter is the separator for the other parameters. The delimiter is placed between the two strings to be connected. The delimiter can be a string or other parameter. If the separator is NULL, the result is NULL. The function ignores the NULL value after any delimiter parameter. But CONCAT_WS() does not ignore any empty strings. However, all NULL will be ignored.
2.1 The concat_ws function can specify the separator between strings for an example demonstration of string splicing:
Specify the separator "-" for splicing:
root@localhost [test02]>SELECT concat_ws('-',1,22,'Hello') display_name;
+--------------+
| display_name |
+--------------+
| 1-22-Hello |
+--------------+
1 row in set (0.00 sec)When the back is empty, it can also be spliced:
root@localhost [test02]>SELECT concat_ws('-',1,22,'Hello','') display_name;
+--------------+
| display_name |
+--------------+
| 1-22-Hello- |
+--------------+
1 row in set (0.00 sec)When the latter is NULL, it can also be spliced:
root@localhost [test02]>SELECT concat_ws('-',1,22,'Hello',NULL) display_name;
+--------------+
| display_name |
+--------------+
| 1-22-Hello |
+--------------+
1 row in set (0.00 sec)NULL can also be specified as a delimiter:
root@localhost [test02]>SELECT concat_ws('NULL',1,22,'Hello',NULL) display_name;
+-------------------+
| display_name |
+-------------------+
| 1NULL22NULL Hello |
+-------------------+
1 row in set (0.01 sec)
root@localhost [test02]>SELECT concat_ws('NULL',1,22,'Hello','') display_name;
+-----------------------+
| display_name |
+-----------------------+
| 1NULL22NULL Hello NULL |
+-----------------------+
1 row in set (0.00 sec)You can also specify null as a delimiter:
root@localhost [test02]>SELECT concat_ws('',1,22,'Hello') display_name;
+--------------+
| display_name |
+--------------+
| 122 Hello |
+--------------+
1 row in set (0.00 sec)Specify stitching headings:
root@localhost [test02]>select * from student3;
+----+------------+---------+------+
| id | teacher_id | name | sex |
+----+------------+---------+------+
| 1 | 1 | xiaosan | male |
| 2 | 2 | xiaosan | male |
| 3 | 3 | Xiaoman | female |
| 4 | 4 | Small talk | female |
+----+------------+---------+------+
4 rows in set (0.00 sec)
root@localhost [test02]>SELECT CONCAT_WS('_',id,name) AS con_ws FROM student3 LIMIT 1;
+-----------+
| con_ws |
+-----------+
| 1_xiaosan |
+-----------+
1 row in set (0.00 sec)
root@localhost [test02]>SELECT CONCAT_WS('_',id,name) FROM student3 LIMIT 1;
+------------------------+
| CONCAT_WS('_',id,name) |
+------------------------+
| 1_xiaosan |
+------------------------+
1 row in set (0.00 sec)2.2 Simple Application Demo:
Mysql Query Data Exported to insert Format File
Create from the test table: CREATE TABLE `student3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teacher_id` int(11) DEFAULT NULL, `name` varchar(20) NOT NULL, `sex` varchar(10) DEFAULT 'male', PRIMARY KEY (`id`), UNIQUE KEY `teacher_id` (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ;
mysql -uroot -p'jianweiwutest' -P3306 -Ne "use test02;select concat_ws('\'','insert into student3 (id,teacher_id,name,sex) values (', id,',',teacher_id,',',name,',',sex,');') sql_str from student3;"
Warning: Using a password on the command line interface can be insecure.
+---------------------------------------------------------------------------------+
| insert into student3 (id,teacher_id,name,sex) values ('1','1','xiaosan','male'); |
| insert into student3 (id,teacher_id,name,sex) values ('2','2','xiaosan','male'); |
+---------------------------------------------------------------------------------+
[root@VM_82_178_centos ~]#
root@localhost [test02]>insert into student3 (id,teacher_id,name,sex) values ('3','3','Xiaoman','female');
Query OK, 1 row affected (0.07 sec)
root@localhost [test02]>insert into student3 (id,teacher_id,name,sex) values ('4','4','Small talk','female');
Query OK, 1 row affected (0.06 sec)
[root@VM_82_178_centos ~]# mysql -uroot -p'jianweiwutest' -P3306 -Ne "use test02;select concat_ws('\'','insert into student3 (id,teacher_id,name,sex) values (', id,',',teacher_id,',',name,',',sex,');') sql_str from student3;"
Warning: Using a password on the command line interface can be insecure.
+---------------------------------------------------------------------------------+
| insert into student3 (id,teacher_id,name,sex) values ('1','1','xiaosan','male'); |
| insert into student3 (id,teacher_id,name,sex) values ('2','2','xiaosan','male'); |
| insert into student3 (id,teacher_id,name,sex) values ('3','3','Xiaoman','female'); |
| insert into student3 (id,teacher_id,name,sex) values ('4','4','Small talk','female'); |
+---------------------------------------------------------------------------------+
[root@VM_82_178_centos ~]#
//Where - N e is the option to execute this SQL statement, - N is the field name without the first line in the output SQL statement execution result, and - E is the field name to execute the SQL statement.GROUP_CONCAT() function
The GROUP_CONCAT function returns a string result, which is composed of value concatenation in the grouping.
root@localhost [test02]>SELECT id,name,sex FROM student3 WHERE id IN('3','4');
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 3 | Xiaoman | female |
| 4 | Small talk | female |
+----+--------+------+
2 rows in set (0.00 sec)Use grammar and features:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])
In MySQL, you can get the join value of the expression combination. Duplicate values can be excluded by using DISTINCT. If you want to sort the values in the results, you can use the ORDER BY clause.
SEPARATOR is a string value that is used to insert into the result value. The default is a comma (","), which can be completely removed by specifying SEPARATOR ".
The maximum length can be set by the variable group_concat_max_len. The syntax executed at run time is as follows: SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
If the maximum length is set, the result value is cut to the maximum length. If the characters of the grouping are too long, the system parameters can be set: SET @@global.group_concat_max_len=40000;
root@localhost [test02]> SELECT id,GROUP_CONCAT(name) FROM student3 WHERE id IN('3','4') GROUP BY id;
+----+--------------------+
| id | GROUP_CONCAT(name) |
+----+--------------------+
| 3 | Xiaoman |
| 4 | Small talk |
+----+--------------------+
2 rows in set (0.00 sec)
root@localhost [test02]> SELECT id,GROUP_CONCAT(name,teacher_id) FROM student3 WHERE id IN('1','4') GROUP BY id;
+----+-------------------------------+
| id | GROUP_CONCAT(name,teacher_id) |
+----+-------------------------------+
| 1 | xiaosan1 |
| 4 | Small talk 4 |
+----+-------------------------------+
2 rows in set (0.00 sec)
root@localhost [test02]> SELECT id,GROUP_CONCAT(name,sex) FROM student3 WHERE id IN('1','4') GROUP BY id;
+----+------------------------+
| id | GROUP_CONCAT(name,sex) |
+----+------------------------+
| 1 | xiaosan male |
| 4 | Little girl |
+----+------------------------+
2 rows in set (0.00 sec)
root@localhost [test02]>SELECT id,GROUP_CONCAT(concat_ws(', ',id,name) ORDER BY id DESC ) FROM student3 WHERE id IN('1','4') GROUP BY id;
+----+---------------------------------------------------------+
| id | GROUP_CONCAT(concat_ws(', ',id,name) ORDER BY id DESC ) |
+----+---------------------------------------------------------+
| 1 | 1, xiaosan |
| 4 | 4, Small talk |
+----+---------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost [test02]>SELECT id,GROUP_CONCAT(concat_ws('.',id,name) ORDER BY id DESC ) FROM student3 WHERE id IN('1','4') GROUP BY id;
+----+--------------------------------------------------------+
| id | GROUP_CONCAT(concat_ws('.',id,name) ORDER BY id DESC ) |
+----+--------------------------------------------------------+
| 1 | 1.xiaosan |
| 4 | 4.Small talk |
+----+--------------------------------------------------------+
2 rows in set (0.01 sec)
root@localhost [test02]>SELECT id,GROUP_CONCAT(concat_ws('.',id,name,sex) ORDER BY id DESC ) FROM student3 WHERE id IN('1','4') GROUP BY id;
+----+------------------------------------------------------------+
| id | GROUP_CONCAT(concat_ws('.',id,name,sex) ORDER BY id DESC ) |
+----+------------------------------------------------------------+
| 1 | 1.xiaosan.male |
| 4 | 4.Small talk.female |
+----+------------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost [test02]>SELECT GROUP_CONCAT(concat_ws('.',id,name,sex) ORDER BY id DESC ) FROM student3 ;
+------------------------------------------------------------+
| GROUP_CONCAT(concat_ws('.',id,name,sex) ORDER BY id DESC ) |
+------------------------------------------------------------+
| 4.Small talk.female,3.Xiaoman.female,2.xiaosan.male,1.xiaosan.male |
+------------------------------------------------------------+
1 row in set (0.00 sec)Welcome to exchange and learn from each other