Tách mảng MySQL

MySQL SUBSTRING_INDEX() trả về chuỗi con từ chuỗi đã cho trước một số lần xuất hiện được chỉ định của dấu phân cách

Chuỗi con được trả về từ bên trái của dấu phân cách cuối cùng khi số được chỉ định là số dương và từ bên phải của dấu phân cách cuối cùng khi số được chỉ định là số âm

Nếu số được chỉ định lớn hơn số lần xuất hiện của dấu phân cách, chuỗi con trả về sẽ là chuỗi tổng. Nếu số được chỉ định là 0, sẽ không có gì được tìm nạp từ chuỗi đã cho

cú pháp

SUBSTRING_INDEX(str, delim, count)

Tranh luận

NameDescriptionstrA chuỗi. delimMột dấu phân cách. countMột số nguyên cho biết số lần xuất hiện của delim

Sơ đồ cú pháp

Phiên bản MySQL. 5. 6

Trình chiếu video

Trình duyệt của bạn không hỗ trợ video HTML5

Thí dụ. Hàm SUBSTRING_INDEX() của MySQL

Câu lệnh MySQL sau đây trả về dạng chuỗi con bên trái của dấu phân cách cuối cùng i. e. dấu phân cách thứ 2 (. ) từ chuỗi đã cho 'www. mytestpage. thông tin'. Đếm bắt đầu từ bên trái của chuỗi

Mã số

SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2);

Đầu ra mẫu

mysql> SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2);
+----------------------------------------------+
| SUBSTRING_INDEX('www.mytestpage.info','.',2) |
+----------------------------------------------+
| www.mytestpage                               | 
+----------------------------------------------+
1 row in set (0.02 sec)

Trình bày bằng hình ảnh

Ví dụ về hàm MySQL SUBSTRING_INDEX() sử dụng số âm

Câu lệnh MySQL sau đây trả về chuỗi con từ bên phải của dấu phân cách cuối cùng i. e. dấu phân cách thứ 2 (. ) từ chuỗi đã cho 'www. mytestpage. thông tin'. Đếm bắt đầu từ bên phải của chuỗi

Mã số

SELECT SUBSTRING_INDEX('www.mytestpage.info','.',-2); 

Đầu ra mẫu

mysql> SELECT SUBSTRING_INDEX('www.mytestpage.info','.',-2); 
+-----------------------------------------------+
| SUBSTRING_INDEX('www.mytestpage.info','.',-2) |
+-----------------------------------------------+
| mytestpage.info                               | 
+-----------------------------------------------+
1 row in set (0.00 sec)

Trình bày bằng hình ảnh

Thí dụ. Tách một địa chỉ IP thành 4 octet tương ứng bằng cách sử dụng hàm MySQL SUBSTRING_INDEX()

Lệnh MySQL sau đây chia địa chỉ IP thành 4 octet tương ứng (đơn vị thông tin kỹ thuật số). Giả sử các địa chỉ IP được lưu trữ trong một bảng mẫu có tên là 'log_file'

Chúng tôi đã làm việc với rất nhiều khách hàng thường xuyên viết SQL. Và thỉnh thoảng họ đến gặp chúng tôi với câu hỏi SQL - mà chúng tôi nghĩ sẽ rất thú vị khi chia sẻ với những người khác

Hôm nay, một trong những khách hàng của chúng tôi đã đến gặp chúng tôi với một câu hỏi - Họ gặp sự cố khi hủy lồng một chuỗi/mảng thành các hàng trong Amazon Redshift

Câu hỏi - Tách chuỗi được phân cách bằng dấu phẩy thành các hàng trong Redshift

Giả sử chúng ta có một bảng books trong Amazon Redshift có những dữ liệu này

tags (varchar)
-----
A
A, B
C, D
E

Và chúng tôi muốn tách/bỏ tổ hợp/nổ nó để nó trở thành như thế này

tag
-----
A
A
B
C
D
E

Trong Postgres, chúng ta có thể sử dụng ngay unnest

select TRIM( UNNEST( STRING_TO_ARRAY(tags, ',') ) ) from books;

Nhưng làm thế nào để chúng tôi làm điều này trong Amazon Redshift (không hỗ trợ unnest)?

Chuỗi thành mảng trong Amazon Redshift

Giải pháp (hoặc cách giải quyết) đang cố chia chuỗi thành nhiều phần

Truy vấn trên sẽ cung cấp cho bạn kết quả chính xác khi sử dụng unnest trong Postgres

Ở đây, NS (dãy số) là một CTE trả về danh sách các số từ 1 đến N, ở đây chúng tôi phải đảm bảo rằng số tối đa của chúng tôi lớn hơn kích thước của các thẻ tối đa của chúng tôi, vì vậy bạn có thể thử thêm nhiều số hơn vào

Vấn đề tương tự trong MySQL

Cách tiếp cận tương tự có thể được áp dụng nếu bạn đang sử dụng MySQL, mặc dù bạn phải thay đổi cú pháp/chức năng một chút

select
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.tags, ',', NS.n), ',', -1)) as tag
from (
  select 1 as n 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 union all
  select 10
) NS
inner join books B ON NS.n <= CHAR_LENGTH(B.tags) - CHAR_LENGTH(REPLACE(B.tags, ',', '')) + 1

Một vài lưu ý

• MySQL không có CTE, vì vậy bạn phải viết NS dưới dạng truy vấn con
• MySQL không có

  tag
  -----
  A
  A
  B
  C
  D
  E
  

  0, vì vậy chúng tôi sử dụng 2

  tag
  -----
  A
  A
  B
  C
  D
  E
  

  1 lồng nhau để đạt được điều tương tự
• MySQL không có

  tag
  -----
  A
  A
  B
  C
  D
  E
  

  2, vì vậy chúng tôi phải làm việc xung quanh một chút để đếm số lượng

  tag
  -----
  A
  A
  B
  C
  D
  E
  

  3 trong chuỗi

Nếu tôi có một mảng JSON của Redshift thì sao?

Được rồi, nếu cột

tag
-----
A
A
B
C
D
E

tags (string with json)
-----
['A']
['A', 'B']
['C', 'D']
['E']

Một cách tiếp cận là bạn chỉ có thể coi chúng là chuỗi và áp dụng cùng một string_split ở trên với một số thao tác làm sạch chuỗi (xóa dấu ngoặc kép, dấu ngoặc vuông). Hoặc bạn có thể sử dụng một số hàm mảng JSON

with NS AS (
  select 1 as n 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 union all
  select 10
)
select
  TRIM(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(B.tags, NS.n - 1)) AS val
from NS
inner join books B ON NS.n <= JSON_ARRAY_LENGTH(B.tags)

Phần kết luận

Làm việc với mảng trong Redshift và MySQL thường rất khó khăn, nhưng hầu hết các trường hợp luôn có cách giải quyết sáng tạo giúp bạn đạt được mục đích tương tự

Tôi đã biên soạn danh sách các ví dụ với dữ liệu mẫu tại đây để bạn tham khảo (bạn có thể sao chép và dán trực tiếp vào thiết bị đầu cuối SQL của mình)

Làm cách nào để tách mảng chuỗi trong MySQL?

Làm cách nào để tách dữ liệu trong MySQL?

Làm cách nào để tách một chuỗi được phân tách bằng dấu phẩy trong MySQL?

Làm cách nào để tách một chuỗi trong thủ tục lưu trữ MySQL?