We all know about the great features such as PHP implode (combines elements of an array into a string delimiter specified) and explode (splits a string into an array for a given separator.)

But sometimes developers have to operate on a delimited string (denormalized data) or vice versa – to receive any data combined with a separator in the database. As it turned out to do this in MySQL is not prosto.Posle little digging in the documentation revealed that for combining records into a string you can use the functions GROUP_CONCAT. The default separator is a comma (symbol “,”). But it can be changed using the parameter SEPARATOR. Here is a small example.

Suppose we have a table with a list of users – «users». From it you must select all the user ID and collect in line with the separator.

CREATE TABLE `users` (
        `Id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `User_name` VARCHAR (200) NULL DEFAULT NULL,
        PRIMARY KEY (`user_id`)
)
COLLATE = ‘utf8_general_ci’
ENGINE = InnoDB
ROW_FORMAT = DEFAULT

And a little filled with data:

Now run the following query to combine the data into a string:

 SELECT GROUP_CONCAT (`id`) as string from` users`

As a result, we obtain the following set of data:

Now combine the data with the character “semicolon” (“;”) running the following query using the parameter SEPARATOR.

 SELECT GROUP_CONCAT (`id` SEPARATOR ‘;’) as string from` users`

As a result, here is a sample taken value:

So using the GROUP_CONCAT can combine any data from database through a separator. Furthermore, the function may be transmitted several fields for their concatenation (bonding). To avoid duplicate entries, you can use the DISTINCT.

This function has a limit on the amount of output data. Default 1024 characters for each association – for each output line. If the size is glued more data, it will be truncated. To expand the size you need to execute a command (only if you have the necessary privileges for the execution)

SET group_concat_max_len = 4096; # The required value

With the incorporation of data on it all. But that would parse through the separator, this will have to tinker longer. Native support for this in MySQL I have not found. The only solution to this – writing a stored procedure to parse the string.

The following is a dump of the stored procedure.

DELIMITER //
CREATE PROCEDURE `explode` (IN` mylist` VARCHAR (255))
body:
BEGIN
  IF mylist = ” THEN LEAVE body; END IF;

  SETsaTail = mylist;

  WHILEsaTail! = ” DO
    SETsHead = SUBSTRING_INDEX (saTail, ‘,’, 1);
    SETsaTail = SUBSTRING (saTail, LENGTH (sHead) + 2);
    ## Then all your code
    ## For example, adding a new user ID in the users table
    INSERT INTO users (id) VALUES (sHead);
  END WHILE;

END //
DELIMITER;

The procedure takes only 1 parameter (for example). This is a list of values ​​separated by commas. After this cycle goes on this parameter and sought separator (in this case – is a comma). Each new value is stored in the variablesHead and it can already be used for SQL queries.

To call the procedure we use the following code.

call explode (‘100000,1010000,1020000’);

After that, the new user ID will appear in the table. This is a fairly simple example. Most of these operations, I use to store denormalized data and their subsequent normalization (via triggers).

Needless search is done on the normalized data, but if you just need to bring information – display denormalized.

This procedure you can change and already use for their needs.

(Visited 4,491 times, 2 visits today)
adminTips
We all know about the great features such as PHP implode (combines elements of an array into a string delimiter specified) and explode (splits a string into an array for a given separator.) But sometimes developers have to operate on a delimited string (denormalized data) or vice versa - to...

Do you want to be notified about new DBA updates, releases, jobs and free tips? Join our email newsletter. It's fast and easy. You will be among the first to know about hot new DBA updates and stuff, it will help you enhance your DBA skills.
We take your privacy very seriously