How to Use MySQL implode, explode
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.
CREATE PROCEDURE `explode` (IN` mylist` VARCHAR (255))
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);
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.