How to solve the NULL problem when concatenating the CONCAT() function in MySQL

The query in the project uses the concat() splicing function. In this query, the spliced ​​field is null. The splicing result is null and a null pointer exception is reported at the application layer.

SELECT CONCAT('1,',NULL,'2') result;

SELECT CONCAT('1,','','2') result;

It has been proved through practice that if the CONCAT() function is spliced ​​with NULL in the spliced ​​parameters, the result will be NULL.

Use the following methods to solve

Method 1: Use the IFNULL function to set it to an empty string if it is NULL.

SELECT CONCAT('1,',IFNULL(NULL,''),'2') result;

Method 2: Use the CONCAT_WS function. Specify string concatenation with delimiters

SELECT CONCAT_WS(',','1',NULL,'2') result;

Specify using commas to separate

##CONCAT_WS(delimiter, parameter 1, parameter 2,. ..Parameter n) and CONCAT (Parameter 1, Parameter 2, ...Parameter n)

The difference between the two functions:

CONCAT_WS() : Indicates concat with separator, that is, string connection with separator

CONCAT(): Indicates concatenation of strings

The two most important differences are, CONCAT() function, splicing If there is NULL in the parameter, NULL

will be returned directly. However, when the CONCAT_WS() function is executed, it will not return NULL

because of the NULL value.

The above is the detailed content of How to solve the NULL problem when concatenating the CONCAT() function in MySQL. For more information, please follow other related articles on the PHP Chinese website!