Hướng dẫn mysql comma separated values to rows - mysql phân tách các giá trị bằng dấu phẩy thành các hàng

Question

Nếu bạn có thể tạo bảng số, chứa các số từ 1 đến các trường tối đa để phân chia, bạn có thể sử dụng một giải pháp như thế này:

Nội dung chính Show

Làm cách nào để chia một chuỗi phân tách dấu phẩy thành nhiều hàng trong MySQL?
Làm thế nào để bạn chia các giá trị phân tách dấu phẩy thành các hàng?
Làm cách nào để phân tách các giá trị phân tách dấu phẩy trong MySQL?
Làm thế nào tôi có thể nhận được nhiều giá trị hàng trong dấu phẩy được phân tách trong MySQL?

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Vui lòng xem Fiddle tại đây.

Nếu bạn không thể tạo bảng, thì một giải pháp có thể là:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

Một ví dụ fiddle là ở đây.

Đã trả lời ngày 30 tháng 7 năm 2013 lúc 9:05Jul 30, 2013 at 9:05

fthiellafthiellafthiella

47.2K15 Huy hiệu vàng90 Huy hiệu bạc103 Huy hiệu đồng15 gold badges90 silver badges103 bronze badges

9

Nếu cột

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

1 là mảng JSON (như

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

2), thì bạn có thể trích xuất/giải nén nó bằng json_table () (có sẵn kể từ MySQL 8.0.4):

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

Result:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

Xem trên DB Fiddle

Nếu bạn lưu trữ các giá trị ở định dạng CSV đơn giản, thì trước tiên bạn sẽ cần chuyển đổi nó thành JSON:

select t.id, j.name
from mytable t
join json_table(
  replace(json_array(t.name), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) j

Result:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

Xem trên DB Fiddle

Nếu bạn lưu trữ các giá trị ở định dạng CSV đơn giản, thì trước tiên bạn sẽ cần chuyển đổi nó thành JSON:Dec 6, 2019 at 10:10

Đã trả lời ngày 6 tháng 12 năm 2019 lúc 10:10Paul Spiegel

Paul Spiegelpaul Spiegel5 gold badges42 silver badges52 bronze badges

4

30.2k5 Huy hiệu vàng42 Huy hiệu bạc52 Huy hiệu Đồng

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 ;

Đã trả lời ngày 30 tháng 7 năm 2013 lúc 9:05Jul 30, 2013 at 9:05

Hướng dẫn mysql comma separated values to rows - mysql phân tách các giá trị bằng dấu phẩy thành các hàng

fthiellafthiellaPrahalad Gaggar

47.2K15 Huy hiệu vàng90 Huy hiệu bạc103 Huy hiệu đồng16 gold badges50 silver badges69 bronze badges

0

Nếu cột

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

1 là mảng JSON (như

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

2), thì bạn có thể trích xuất/giải nén nó bằng json_table () (có sẵn kể từ MySQL 8.0.4):

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)
group_name from N, T

Xem trên DB FiddleAug 1, 2019 at 14:07

Nếu bạn lưu trữ các giá trị ở định dạng CSV đơn giản, thì trước tiên bạn sẽ cần chuyển đổi nó thành JSON:Harry Marx

Đã trả lời ngày 6 tháng 12 năm 2019 lúc 10:101 silver badge2 bronze badges

3

Paul Spiegelpaul Spiegel

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 ;

30.2k5 Huy hiệu vàng42 Huy hiệu bạc52 Huy hiệu Đồng

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;

Tôi đã lấy tài liệu tham khảo từ đây với tên cột đã thay đổi.

Prahalad Gaggarprahalad Gaggar6 gold badges49 silver badges81 bronze badges

11.1K16 Huy hiệu vàng50 Huy hiệu bạc69 Huy hiệu ĐồngFeb 5, 2017 at 12:57

Đây là nỗ lực của tôi: Chọn đầu tiên trình bày trường CSV cho sự phân chia. Sử dụng CTE đệ quy, chúng ta có thể tạo một danh sách các số được giới hạn ở số lượng điều khoản trong trường CSV. Số lượng các thuật ngữ chỉ là sự khác biệt trong độ dài của trường CSV và chính nó với tất cả các phân định bị xóa. Sau đó tham gia với các số này, Subring_index trích xuất thuật ngữ đó.Andrey

Đã trả lời ngày 1 tháng 8 năm 2019 lúc 14:0717 silver badges13 bronze badges

1

Harry Marxharry Marx

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

0

991 Huy hiệu bạc2 Huy hiệu đồng

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

Biến thể của tôi: Quy trình được lưu trữ lấy tên bảng, tên trường và dấu phân cách làm đối số. Lấy cảm hứng từ bài đăng http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-lows/Jul 5 at 10:14

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

2

Ví dụ sử dụng (bình thường hóa):Oct 15, 2016 at 8:26

AshkufarazImanez

5.1346 Huy hiệu vàng49 Huy hiệu bạc81 Huy hiệu đồng5 silver badges13 bronze badges

Đã trả lời ngày 5 tháng 2 năm 2017 lúc 12:57

Andreyandrey

1.42517 Huy hiệu bạc13 Huy hiệu đồng

Bởi vì bạn phải tiếp tục thêm "Chọn số liên minh" trong ví dụ trên có thể là một vấn đề nếu bạn cần một số lượng lớn các lần chia tách.

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

3

Tôi đã quyết định một cách tốt hơn là điều này chỉ thêm một hàng số cho mỗi chữ số. Ví dụ dưới đây là tốt cho 1-1000 Thêm một hàng khác làm cho nó tốt cho 1-10000, v.v.May 29, 2019 at 20:11

2

Đã trả lời ngày 5 tháng 7 lúc 10:14

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

4

Đã trả lời ngày 15 tháng 10 năm 2016 lúc 8:26

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

5

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

6

Imanezimanez

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

7

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

8

5005 huy hiệu bạc13 huy hiệu đồngAug 18, 2020 at 7:00

Câu hỏi ban đầu là cho MySQL và SQL nói chung. Ví dụ dưới đây là cho các phiên bản mới của MySQL. Thật không may, một truy vấn chung sẽ hoạt động trên bất kỳ máy chủ SQL nào là không thể. Một số máy chủ không hỗ trợ CTE, các máy chủ khác không có Subring_index, nhưng các máy chủ khác có chức năng tích hợp để chia chuỗi thành nhiều hàng.

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 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

9

--- câu trả lời sau ---Nov 14, 2018 at 20:25

1

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

0

Các truy vấn đệ quy thuận tiện khi máy chủ không cung cấp chức năng tích hợp. Họ cũng có thể là nút cổ chai.

Truy vấn sau đây đã được viết và kiểm tra trên MySQL phiên bản 8.0.16. Nó sẽ không hoạt động trên phiên bản 5.7-. Các phiên bản cũ không hỗ trợ biểu thức bảng chung (CTE) và do đó truy vấn đệ quy.Aug 19, 2021 at 18:46

1

Làm cách nào để chia một chuỗi phân tách dấu phẩy thành nhiều hàng trong MySQL?

chuỗi phân chia-into-rows.sql-- chia một chuỗi được phân tách bằng dấu phẩy (còn gọi là "set"), $ strlist và trả về phần tử (AKA Subring) khớp với chỉ mục được cung cấp, $ i.sql -- Splits a comma-separated string (AKA "SET"), $strlist, and returns the element (aka substring) matching the provided index, $i.

Làm thế nào để bạn chia các giá trị phân tách dấu phẩy thành các hàng?

Trong hộp thoại phân chia ô, chọn chia thành các hàng hoặc chia thành các cột trong phần loại khi bạn cần.Và trong phần Chỉ định phần Dấu phân cách, chọn tùy chọn khác, nhập ký hiệu dấu phẩy vào hộp văn bản, sau đó nhấp vào nút OK.

Làm cách nào để phân tách các giá trị phân tách dấu phẩy trong MySQL?

Cách chia văn bản phân tách dấu phẩy (danh sách ID) trong quy trình lưu trữ MySQL để sử dụng kết quả trong câu lệnh SQL "in".Chọn * từ bảng trong đó bảng.id in (splitStringFunction (CommaseParatedData, ','));SQL.SELECT * FROM table WHERE table.id IN (splitStringFunction(commaSeparatedData, ',')); sql.

Làm thế nào tôi có thể nhận được nhiều giá trị hàng trong dấu phẩy được phân tách trong MySQL?

Làm thế nào để có được nhiều hàng vào danh sách phân tách dấu phẩy trong SQL..

Mysql.MySQL có chức năng Group_concat () cho phép chúng tôi xuất kết quả truy vấn của mình trong danh sách phân tách dấu phẩy: chọn nhóm_concat (petName) từ vật nuôi;....

Cơ sở dữ liệu Oracle.....

Máy chủ SQL.....

Mariadb.....

Postgresql.....

Sqlite.....

Nhiều cột ..

programming mysql SQL fiddle

Hướng dẫn mysql comma separated values to rows - mysql phân tách các giá trị bằng dấu phẩy thành các hàng

Làm cách nào để chia một chuỗi phân tách dấu phẩy thành nhiều hàng trong MySQL?

Làm thế nào để bạn chia các giá trị phân tách dấu phẩy thành các hàng?

Làm cách nào để phân tách các giá trị phân tách dấu phẩy trong MySQL?

Làm thế nào tôi có thể nhận được nhiều giá trị hàng trong dấu phẩy được phân tách trong MySQL?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội