Hướng dẫn split comma separated values in mysql - chia các giá trị được phân tách bằng dấu phẩy trong mysql

Blog

Tách các giá trị phân tách bằng dấu phẩy trong MySQL

Bởi Đội Sisense

Xem Sisense trong hành động

Đăng ký dùng thử miễn phí để xây dựng trải nghiệm trực quan, tương tác.

Bằng cách kiểm tra hộp này, tôi đồng ý rằng chi tiết liên lạc của tôi có thể được sử dụng bởi Sisense và các chi nhánh của nó để gửi cho tôi tin tức về các sản phẩm và dịch vụ của Sisense và các thông tin tiếp thị khác.

Bằng cách nhấp vào nút Gửi bên dưới, tôi xác nhận rằng tôi đã đọc và hiểu chính sách bảo mật và điều khoản dịch vụ của Sisense.

Nhận mới nhất trong phân tích ngay trong hộp thư đến của bạn.

Bằng cách kiểm tra hộp này, tôi đồng ý rằng chi tiết liên lạc của tôi có thể được sử dụng bởi Sisense và các chi nhánh của nó để gửi cho tôi tin tức về các sản phẩm và dịch vụ của Sisense và các thông tin tiếp thị khác.

Bằng cách nhấp vào nút Gửi bên dưới, tôi xác nhận rằng tôi đã đọc và hiểu chính sách bảo mật và điều khoản dịch vụ của Sisense.

Tôi có dữ liệu phân tích cú pháp với dấu gạch nối trong đó. Ví dụ dưới đây sử dụng một chuỗi văn bản cố định để chứng minh, chỉ cần thay đổi các tham chiếu thành tên cột có liên quan trong bảng. Tôi đã chơi từ lâu với một cách để đảm bảo nó hoạt động trên các mã với số lượng thành phần khác nhau và cuối cùng đã quyết định thêm mệnh đề WHERE. Hầu hết dữ liệu bạn đang cố gắng phân tích phân tích sẽ có một số cột cố định.

select
SUBSTRING_INDEX(TS,"-",1) as "1",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",2)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",2)))-1)) as "2",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",3)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",3)))-1)) as "3",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",4)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",4)))-1)) as "4",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",5)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",5)))-1)) as "5",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",6)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",6)))-1)) as "6",reverse(left(reverse(SUBSTRING_INDEX(TS,"-",7)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",7)))-1)) as "7",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",8)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",8)))-1)) as "8",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",9)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",9)))-1)) as "9",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",10)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",10)))-1)) as "10"
from (select "aaa-bbb-ccc-ddd-eee-fff-ggg-hhh-iii-jjj" as TS) as S
where (LENGTH(TS)-LENGTH(REPLACE(TS,'-',''))) =9

Chia các giá trị phân tách dấu phẩy và lấy một giá trị ở vị trí nhất định

Đáng ngạc nhiên là MySQL không có chức năng dành riêng cho các hoạt động này trái ngược với Split_Part trong PostgreSQL. May mắn thay, nó có chức năng Subring_index () mà gần như những gì chúng ta cần.

Từ tài liệu chính thức:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

1

Trả về chuỗi con từ String Str trước khi tính toán của DELIM DELIMITER. Nếu số lượng là tích cực, mọi thứ ở bên trái của dấu phân cách cuối cùng (đếm từ bên trái) được trả về. Nếu số lượng là âm, mọi thứ ở bên phải của dấu phân cách cuối cùng (đếm từ bên phải) được trả về.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

2 thực hiện một trận đấu nhạy cảm trường hợp khi tìm kiếm Delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

Chức năng này là multibyte an toàn.

Định nghĩa

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

Dung dịch

Giả sử chúng tôi muốn tìm tất cả nhân viên làm việc ở một trạng thái nhất định (ví dụ:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

3) là trường thứ tư trong một địa chỉ giữ cột dưới dạng các giá trị được phân tách bằng dấu phẩy.

Với chức năng

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

4, chúng tôi sẽ có thể trích xuất mọi thứ lên đến cột thứ tư chứa trạng thái

SELECT SUBSTRING_INDEX(address, ',', 4) FROM employees;
+-----------+------------------------------------+
| name      | SUBSTRING_INDEX(address, ',', 4)   |
+-----------+------------------------------------+
| John Doe  | 4225 Collins Street,Apt. A,Erie,PA | 
| Bob Smith | 234 Main Street,,Erie,PA           | 
+-----------+------------------------------------+

Bây giờ chúng ta biết rằng trạng thái sẽ luôn là trường cuối cùng trong giá trị kết quả. Vì hàm

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

2 cho phép sử dụng các giá trị âm để trích xuất các trường đếm từ bên phải, chúng tôi sẽ sử dụng khả năng này để trích xuất trường ngoài cùng có chứa chữ viết tắt trạng thái.

SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1) AS state FROM employees;
+-----------+-------+
| name      | state |
+-----------+-------+
| John Doe  | PA    | 
| Bob Smith | PA    | 
+-----------+-------+

Giải pháp cuối cùng để tất cả nhân viên làm việc ở một tiểu bang nhất định được hiển thị dưới đây.

SELECT name FROM employees WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1)='PA';
+-----------+
| name      |
+-----------+
| John Doe  | 
| Bob Smith | 
+-----------+

Hãy cẩn thận

Một điều cần lưu ý về

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

2 là hàm trả về toàn bộ chuỗi nếu trường được yêu cầu không tồn tại.

Ví dụ,

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

7 trả về địa chỉ đầy đủ.

SELECT name, SUBSTRING_INDEX(address, ',', 6) FROM employees;
+-----------+------------------------------------------+
| name      | SUBSTRING_INDEX(address, ',', 6)         |
+-----------+------------------------------------------+
| John Doe  | 4225 Collins Street,Apt. A,Erie,PA,16510 | 
| Bob Smith | 234 Main Street,,Erie,PA,16510           | 
+-----------+------------------------------------------+

Sau đó, cố gắng trích xuất trường thứ sáu với

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

8 sẽ cung cấp cho chúng tôi mã zip (trường thứ năm) sai.

SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1) AS country FROM employees;
+-----------+---------+
| name      | country |
+-----------+---------+
| John Doe  | 16510   | 
| Bob Smith | 16510   | 
+-----------+---------+

Một cách giải quyết có thể xảy ra với vấn đề này là kiểm tra xem số lượng trường lớn hơn hoặc bằng với chỉ mục được yêu cầu và sau đó mã trên hoặc trả về chuỗi trống, nếu chỉ mục trường không hợp lệ.

SELECT 
   name, 
   IF(
      LENGTH(address) - LENGTH(REPLACE(address, ',', '')) + 1 >= 6,
      SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1),
      ''
   ) AS country 
FROM employees;
+-----------+---------+
| name      | country |
+-----------+---------+
| John Doe  |         | 
| Bob Smith |         | 
+-----------+---------+

Có thể sử dụng

Trong hầu hết các trường hợp, tốt hơn là sử dụng một bảng tra cứu riêng biệt hoặc một cột cho mỗi trường thay vì lưu trữ dữ liệu dưới dạng các giá trị được phân tách bằng dấu phẩy để tra cứu sau này.

Tuy nhiên, có thể có những trường hợp hiếm hoi trong đó thủ thuật trên có thể hữu ích. Ví dụ, gần đây tôi cần trả về danh sách các giá trị từ hàm do người dùng xác định và cách duy nhất để làm điều đó là trả về một chuỗi chứa các giá trị được phân tách bằng dấu phẩy. Cuối cùng tôi đã sử dụng

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

2 để đặt hàng kết quả theo một trong các trường.

Tìm kiếm trong một cột chứa các giá trị được phân tách bằng dấu phẩy

MySQL có chức năng chuyên dụng find_in_set () trả về chỉ mục trường nếu giá trị được tìm thấy trong một chuỗi chứa các giá trị được phân tách bằng dấu phẩy.

Ví dụ: câu lệnh sau trả về chỉ mục một dựa trên giá trị

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

0 trong chuỗi

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

1.

SELECT FIND_IN_SET('C', 'A,B,C,D') AS result;
+--------+
| result |
+--------+
|      3 | 
+--------+

Nếu giá trị đã cho không được tìm thấy, hàm

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

2 trả về

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(255),
   address TEXT,
   PRIMARY KEY (id)
);

INSERT INTO employees (id, name, address) VALUES 
(1, 'John Doe', '4225 Collins Street,Apt. A,Erie,PA,16510'),
(2, 'Bob Smith', '234 Main Street,,Erie,PA,16512');

3.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

0

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 phân tách dấu phẩy phân tách các giá trị chuỗi thành các cột trong MySQL?

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 phân tách dấu phẩy tách các giá trị trong truy vấn SQL thành các hàng?