The way MySQL combines multiple fields into a single record

Creating a Database

The code is as follows	Copy Code
CREATE TABLE IF not EXISTS ' tet ' ( ' id ' int (one) not NULL, ' Name ' varchar (255) Not NULL, ' URL ' varchar (255) Not NULL ) Engine=innodb DEFAULT Charset=utf8; The data in the Dump table ' tet ' INSERT into ' tet ' (' id ', ' name ', ' url ') VALUES (1, ' Baidu ', ' http://www.111cn.net '), (0, ' Google ', ' http://www.google.com.hk '), (3, ' 400 telephone ', ' http://www.111cn.net ');

Method One:

The code is as follows

Copy Code

SELECT Group_concat (name) name From Tet WHERE 11 = 1 LIMIT 0, 30 Result: Name Baidu, google,400 telephone. Group_concat can also specify connectors with separator keywords, and the SQL statements are as follows: SELECT group_concat (url SEPARATOR "@") URL From Tet WHERE 11 = 1 LIMIT 0, 30 Results: http://www.111cn.net @http://www.google.com.hk@ http://www.111cn.net Method II: SELECT Group_concat (name) name From Tet WHERE 11 = 1 GROUP by ID LIMIT 0, 30

Results: Google Baidu 400 phone about MySQL database will be a number of records of a single field into a record of the operation is introduced here, I hope this introduction can be harvested for you!

Method Two, Concat function

How to use:
CONCAT (STR1,STR2,...)

Returns a string resulting from a connection parameter. If any of the arguments are null, the return value is null.

Attention:
If all parameters are non binary strings, the result is a non binary string.
If the argument contains any twos feed string, the result is a binary string.
A numeric parameter is converted to an equal binary string format; To avoid this, you can use explicit type cast, for example:
SELECT CONCAT (CAST (Int_col as CHAR), Char_col)

MySQL's concat function can connect one or more strings, such as

The code is as follows	Copy Code
Mysql> Select Concat (' 10 '); +--------------+ | Concat (' 10 ') | +--------------+ | 10 | +--------------+ 1 row in Set (0.00 sec) Mysql> Select concat (' 11 ', ' 22 ', ' 33 '); +------------------------+ | Concat (' 11 ', ' 22 ', ' 33 ') | +------------------------+ | 112233 | +------------------------+ 1 row in Set (0.00 sec)

The MySQL concat function returns null if one of the strings is null when the string is concatenated

The code is as follows	Copy Code
Mysql> Select Concat (' One ', ' all ', null); +------------------------+ | Concat (' One ', ', ', null) | +------------------------+ | NULL | +------------------------+ 1 row in Set (0.00 sec)

Concat_ws function in MySQL

How to use:

Concat_ws (SEPARATOR,STR1,STR2,...)

Concat_ws () represents CONCAT with Separator and is a special form of CONCAT (). The first parameter is the separator for the other arguments. The position of the separator is placed between the two strings to which you want to connect. The delimiter can be a string, or it can be another parameter.
Attention:
If the delimiter is null, the result is null. function ignores NULL values after any of the delimiter parameters.

Separated by commas after connection

code is as follows

copy code

mysql> Select Concat_ws (', ', ', ', ', ', ', ', ') '; +-------------------------------+ | concat_ws (', ', ', ', ', ', ') ' | +-------------------------------+ | 11,22,33 | The +-------------------------------+ 1 row in Set (0.00 sec) differs from the concat function in MySQL, when the CONCAT_WS function is executed Null is not returned for null mysql> Select Concat_ws (', ', ' one ', ' all ', NULL); +-------------------------------+ | Concat_ws (', ', ' One ', ' ', ', NULL ') | +-------------------------------+ | 11,22 | +-------------------------------+ 1 row in Set (0.00 sec)