In MySQL, list_add() is a system function that adds a value to a comma-separated list of values and returns the result.

So it’s a handy function that allows us to append a value to a list.

Syntax

The syntax goes like this:

list_add(list, value)

Where list is the list for which to add the value, and value is the value to add.

Example 1

Here’s a basic example to demonstrate the concept:

SELECT sys.list_add('Cat,Dog,Cow', 'Bird');

Result:

Cat,Dog,Cow,Bird

So, the first argument is the list, and the second argument is the value that we added to that list.

Example 2

The list_add() function can be useful for adding items to system variables that store a comma-separated list of values. For example, we can use list_add() to add a value to our sql_mode system variable.

Suppose we want to add PIPES_AS_CONCAT to our sql_mode.

Let’s first check the current value of sql_mode:

SELECT @@sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

So it doesn’t yet have PIPES_AS_CONCAT. Let’s change that:

SET @@sql_mode = sys.list_add(@@sql_mode, 'PIPES_AS_CONCAT');
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

So we successfully appended the value to our sql_mode.