Вопрос или проблема
У меня есть таблица:
id | name
1 | a,b,c
2 | b
Я хочу получить вывод следующие образом:
id | name
1 | a
1 | b
1 | c
2 | b
Если вы можете создать таблицу чисел, которая содержит числа от 1 до максимального количества полей для разделения, вы можете использовать решение, подобное этому:
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) as name
from
numbers inner join tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
order by
id, n
Пожалуйста, смотрите фиддл здесь.
Если вы не можете создать таблицу, тогда решение может быть таким:
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) as name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
order by
id, n
Пример фиддла здесь.
Если столбец name был бы массивом JSON (например, '["a","b","c"]'), то вы могли бы извлечь/распаковать его с помощью JSON_TABLE() (доступно с MySQL 8.0.4):
select t.id, j.name
from mytable t
join json_table(
t.name,
'$[*]' columns (name varchar(50) path '$')
) j;
Результат:
| id | name |
| --- | ---- |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | b |
Если вы храните значения в простом формате CSV, то сначала вам нужно будет конвертировать его в JSON:
select t.id, j.name
from mytable t
join json_table(
replace(json_array(t.name), ',', '","'),
'$[*]' columns (name varchar(50) path '$')
) j
Результат:
| id | name |
| --- | ---- |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | b |
Вот моя попытка:
Первый select представляет csv поле для разделения.
Используя рекурсивный CTE, мы можем создать список чисел, который ограничен количеством терминов в поле csv.
Количество терминов равно разнице в длине поля csv и самого себя без всех разделителей.
Затем, соединяясь с этими числами, substring_index извлекает этот термин.
with recursive
T as ( select 'a,b,c,d,e,f' as items),
N as ( select 1 as n union select n + 1 from N, T
where n <= length(items) - length(replace(items, ',', '')))
select distinct substring_index(substring_index(items, ',', n), ',', -1) as group_name from N, T
Я взял за основу это решение с измененным именем столбца.
DELIMITER $$
CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER)
RETURNS VARCHAR(65000)
BEGIN
DECLARE output VARCHAR(65000);
SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
, LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
, delim
, '');
IF output="" THEN SET output = null; END IF;
RETURN output;
END $$
CREATE PROCEDURE BadTableToGoodTable()
BEGIN
DECLARE i INTEGER;
SET i = 1;
REPEAT
INSERT INTO GoodTable (id, name)
SELECT id, strSplit(name, ',', i) FROM BadTable
WHERE strSplit(name, ',', i) IS NOT NULL;
SET i = i + 1;
UNTIL ROW_COUNT() = 0
END REPEAT;
END $$
DELIMITER ;
Мой вариант: хранимая процедура, которая принимает имя таблицы, имена полей и разделитель в качестве аргументов. Вдохновлено постом http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/
delimiter $$
DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value VARCHAR(255);
DECLARE occurrences INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM
tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
id_column,' id, ', value_column,' value FROM ',tablename);
PREPARE stmt FROM @expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TEMPORARY TABLE IF EXISTS tmp_table2;
CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;
OPEN cur;
read_loop: LOOP
FETCH cur INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurrences = (SELECT CHAR_LENGTH(value) -
CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
SET i=1;
WHILE i <= occurrences DO
SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
SUBSTRING_INDEX(value, delim, i), delim, -1)));
INSERT INTO tmp_table2 VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM tmp_table2;
CLOSE cur;
DROP TEMPORARY TABLE tmp_table1;
END; $$
delimiter ;
Пример использования (нормализация):
CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');
CREATE TABLE interests (
interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(30) NOT NULL
) SELECT DISTINCT value interest FROM tmp_table2;
CREATE TABLE contact_interest (
contact_id INT NOT NULL,
interest_id INT NOT NULL,
CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
) SELECT my_contacts.contact_id, interests.interest_id
FROM my_contacts, tmp_table2, interests
WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;
Поскольку вам постоянно нужно добавлять "select number union all" в приведённом выше примере, это может быть проблемой, если вам требуется большое количество разбиений.
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) as name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
order by
id, n
Я решил, что лучшим способом будет это, что добавляет строку номера для каждой цифры. Пример ниже хорош для 1-1000, добавление другой строки делает его хорошим для 1-10000 и так далее.
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) as name
from(SELECT @row := @row + 1 AS n FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t3,
(SELECT @row:=0) as numbers) as numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
order by
id, n
Вот еще один трюк. Число 20 - это максимальное количество значений в списке, разделенном запятыми.
Мы используем единый запрос, без процедур.
Если tbl имеет больше строк, чем максимальное количество значений в одном списке, разделенном запятыми, вы можете убрать часть "inner join tbl a inner join tbl c" из запроса. Я добавил это, потому что строк всего 2.
CREATE TABLE tbl(id int NOT NULL,name varchar(50),PRIMARY KEY (`id`));
insert into tbl values(1, 'a,b,c'), (2, 'd');
select id ,SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', k.n), ',', -1) as name
from tbl
INNER JOIN (
SELECT *
FROM (
SELECT @n:=@n+1 AS n
FROM tbl inner join tbl a inner join tbl c
INNER JOIN (SELECT @n:=0) AS _a
) AS _a WHERE _a.n <= 20
) AS k ON k.n <= LENGTH(name) - LENGTH(replace(name, ',','')) + 1
order by id
Это трюк для извлечения n-го значения в списке, разделенном запятыми:
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', k.n), ',', -1)
CREATE PROCEDURE `getVal`()
BEGIN
declare r_len integer;
declare r_id integer;
declare r_val varchar(20);
declare i integer;
DECLARE found_row int(10);
DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
create table x(id int,name varchar(20));
open row;
select FOUND_ROWS() into found_row ;
read_loop: LOOP
IF found_row = 0 THEN
LEAVE read_loop;
END IF;
set i = 1;
FETCH row INTO r_len,r_id,r_val;
label1: LOOP
IF i <= r_len THEN
insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
SET i = i + 1;
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
set found_row = found_row - 1;
END LOOP;
close row;
select * from x;
drop table x;
END
Оригинальный вопрос касался MySQL и SQL в целом. Приведенный ниже пример предназначен для новых версий MySQL. К сожалению, универсальный запрос, который работал бы на любом SQL-сервере, невозможен. Некоторые серверы не поддерживают CTE, другие не имеют substring_index, ещё другие имеют встроенные функции для разделения строки на несколько строк.
--- ответ следует ---
Рекурсивные запросы удобны, когда сервер не предоставляет встроенные функции. Они также могут быть узким местом.
Следующий запрос был написан и протестирован на MySQL версии 8.0.16. Он не будет работать на версии 5.7-. Старые версии не поддерживают общее выражение таблицы (CTE) и, следовательно, рекурсивные запросы.
with recursive
input as (
select 1 as id, 'a,b,c' as names
union
select 2, 'b'
),
recurs as (
select id, 1 as pos, names as remain, substring_index( names, ',', 1 ) as name
from input
union all
select id, pos + 1, substring( remain, char_length( name ) + 2 ),
substring_index( substring( remain, char_length( name ) + 2 ), ',', 1 )
from recurs
where char_length( remain ) > char_length( name )
)
select id, name
from recurs
order by id, pos;
Вот моё решение
-- Создайте максимальное количество слов, которые мы хотим выбрать (индексы в n)
with recursive n(i) as (
select
1 i
union all
select i+1 from n where i < 1000
)
select distinct
s.id,
s.oaddress,
-- n.i,
-- используйте индекс для выбора n-го слова, последние слова всегда будут повторяться. Удалите дубликаты с distinct
if(instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' ') > 0,
reverse(substr(reverse(trim(substring_index(s.oaddress,' ',n.i))),1,
instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' '))),
trim(substring_index(s.oaddress,' ',n.i))) as oth
from
app_schools s,
n
Лучшие практики.
Результат:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
FROM
(
SELECT @xi:=@xi+1 as help_id from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0
) a
WHERE
help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1
Сначала создайте таблицу чисел:
SELECT @xi:=@xi+1 as help_id from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0;
| help_id |
| --- |
| 0 |
| 1 |
| 2 |
| 3 |
| ... |
| 24 |
Во-вторых, просто разделите строку:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
FROM
numbers_table
WHERE
help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1
| oid |
| --- |
| ab |
| bc |
| cd |
Самый простой и правильный способ сделать это в MySQL - использовать рекурсивный CTE.
WITH RECURSIVE SplitValues AS (
SELECT
id,
SUBSTRING_INDEX(name, ',', 1) AS split_value,
IF(LOCATE(',', name) > 0, SUBSTRING(name, LOCATE(',', name) + 1), NULL) AS remaining_values
FROM tmpNumber
UNION ALL
SELECT
id,
SUBSTRING_INDEX(remaining_values, ',', 1) AS split_value,
IF(LOCATE(',', remaining_values) > 0, SUBSTRING(remaining_values, LOCATE(',', remaining_values) + 1), NULL)
FROM
SplitValues
WHERE
remaining_values IS NOT NULL
)
SELECT
id,
split_value
FROM
SplitValues order by id;
SELECT id, unnest(string_to_array(name, ',')) AS names
FROM datatable
Надеюсь, это поможет 😀
Ответ или решение
Для решения задачи по разбивке значений в столбце базы данных SQL на несколько строк, мы можем использовать различные подходы в зависимости от используемой СУБД, ее версии и особенностей. Ниже представлены несколько способов реализации данного процесса в MySQL, который часто используется для работы с данными в формате CSV.
Исходные данные
Предположим, у нас есть таблица с данными следующего формата:
CREATE TABLE my_table (
id INT,
name VARCHAR(255)
);
INSERT INTO my_table (id, name) VALUES
(1, 'a,b,c'),
(2, 'b');Ожидаемый результат
Мы хотим получить данные в следующем формате:
id | name
---------
1 | a
1 | b
1 | c
2 | bСпособы решения проблемы
1. Использование рекурсивного CTE (для MySQL 8.0 и выше)
Самый простой и элегантный способ разбивки строк — использование рекурсивных CTE (Common Table Expressions). Ниже приведен пример реализации:
WITH RECURSIVE SplitValues AS (
SELECT
id,
SUBSTRING_INDEX(name, ',', 1) AS split_value,
IF(LOCATE(',', name) > 0, SUBSTRING(name, LOCATE(',', name) + 1), NULL) AS remaining_values
FROM my_table
UNION ALL
SELECT
id,
SUBSTRING_INDEX(remaining_values, ',', 1) AS split_value,
IF(LOCATE(',', remaining_values) > 0, SUBSTRING(remaining_values, LOCATE(',', remaining_values) + 1), NULL)
FROM
SplitValues
WHERE
remaining_values IS NOT NULL
)
SELECT
id,
split_value
FROM
SplitValues
ORDER BY id;2. Использование пользовательской функции и процедуры
Если у вас старая версия MySQL, вы можете использовать пользовательские функции и процедуры. Пример такой процедуры:
DELIMITER $$
CREATE PROCEDURE SplitValuesProc()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE temp_id INT;
DECLARE temp_name VARCHAR(255);
DECLARE cur CURSOR FOR SELECT id, name FROM my_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO temp_id, temp_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @i = 1;
WHILE CHAR_LENGTH(temp_name) > 0 DO
SET @name_part = SUBSTRING_INDEX(temp_name, ',', 1);
INSERT INTO my_transformed_table (id, name) VALUES (temp_id, @name_part);
SET temp_name = SUBSTRING(temp_name, LENGTH(@name_part) + 2);
SET @i = @i + 1;
END WHILE;
END LOOP;
CLOSE cur;
END $$
DELIMITER ;
CALL SplitValuesProc();3. Использование подзапросов и временных таблиц
Если не существует возможности использовать CTE или процедуры, можно воспользоваться временными таблицами и подзапросами для разбивки строк:
SELECT
id,
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', numbers.n), ',', -1) AS name
FROM
(SELECT 1 n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4) AS numbers -- Предполагается максимальное количество значений 4
INNER JOIN my_table ON
CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, ',', '')) >= numbers.n - 1
ORDER BY id, n;Заключение
Существует несколько способов разбить значения в столбце на несколько строк в зависимости от возможностей вашей базы данных и специфики задачи. Самый современный и лучший способ — использовать рекурсивные CTE, если ваша версия MySQL поддерживает это. В противном случае есть альтернативы, такие как пользовательские функции и процедуры, или простые подзапросы с использованием временных таблиц.
Эти методы помогут вам эффективно нормализовать данные в вашем приложении, улучшая их доступность для анализа и обработки.