Đặt chuỗi được phân tách bằng dấu phẩy trong IN() như trong cú pháp bên dưới Show
select *from yourTableName where yourColumnName IN('yourCommaSeparatedValue');Trước tiên chúng ta hãy tạo một bảng - mysql> create table DemoTable1314 -> ( -> Number varchar(100) -> ); Query OK, 0 rows affected (0.53 sec) Chèn một số bản ghi vào bảng bằng lệnh chèn - mysql> insert into DemoTable1314 values('45,67,89');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1314 values('10,20,50');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1314 values('90,56,45');
Query OK, 1 row affected (0.23 sec)Hiển thị tất cả các bản ghi từ bảng bằng câu lệnh select - mysql> select *from DemoTable1314; đầu ra+----------+ | Number | +----------+ | 45,67,89 | | 10,20,50 | | 90,56,45 | +----------+ 3 rows in set (0.00 sec) Sau đây là truy vấn sử dụng chuỗi được phân tách bằng dấu phẩy trong MySQL `IN()` mysql> select *from DemoTable1314 where Number IN('10,20,50');
đầu ra+----------+ | Number | +----------+ | 10,20,50 | +----------+ 1 row in set (0.02 sec)  Các nhà phát triển cơ sở dữ liệu thường cần chuyển đổi một giá trị được phân tách bằng dấu phẩy hoặc các mục được phân tách khác thành định dạng bảng. Dấu phân cách bao gồm đường ống “. ”, hàm băm “#”, đô la “$” và các ký tự khác. Khả năng tương thích của SQL Server cấp 130 và các phiên bản tiếp theo, hỗ trợ hàm string_split() để chuyển đổi các giá trị được phân tách bằng dấu phân cách thành hàng (định dạng bảng). Đối với các mức độ tương thích dưới 130, trước đây các nhà phát triển đã thực hiện việc này bằng chức năng do người dùng xác định, kết hợp vòng lặp While hoặc Con trỏ để trích xuất dữ liệu Tại sao đầu vào được phân tách bằng dấu phẩy cần thiết cho một thủ tục hoặc truy vấn T-SQL?Thực thi một chương trình trong một lần lặp sử dụng vòng lặp là một phương pháp phổ biến trong ứng dụng back-end cũng như cơ sở dữ liệu. Khi số lần lặp nhỏ hơn dự kiến, thời gian thực hiện có thể ít hơn tuy nhiên số lần lặp lớn hơn dẫn đến thời gian xử lý lâu hơn. Ví dụ: nếu ứng dụng đang tìm nạp dữ liệu từ cơ sở dữ liệu bằng cách thực thi một đoạn chương trình trong một vòng lặp bằng cách phân tích cú pháp tham số đầu vào khác nhau, ứng dụng sẽ cần bao bọc phản hồi cơ sở dữ liệu của mỗi lần lặp. Để xử lý loại tình huống này, chúng ta có thể thực thi cùng một chương trình với một lần thực thi duy nhất bằng cách phân tích cú pháp các giá trị được phân tách bằng dấu phẩy hoặc dấu phân cách làm tham số đầu vào trong cơ sở dữ liệu và biến nó thành định dạng bảng trong chương trình T-SQL để tiếp tục với logic tiếp theo. Ngày nay, nhiều nền tảng đã được thay đổi thành kiến trúc vi dịch vụ. Không chỉ ứng dụng mà thiết kế cơ sở dữ liệu cũng có thể được cấu trúc theo kiến trúc dựa trên dịch vụ vi mô và hầu hết giao tiếp nội bộ giữa các cơ sở dữ liệu chỉ được thực hiện với các tham chiếu số nguyên. Đó là lý do để có được một số cách phức tạp để đạt được hiệu quả thực thi tốt hơn từ phía cơ sở dữ liệu Có thể thực hiện nhiều đầu vào cho cùng một tham số bằng các giá trị được phân tách bằng dấu phẩy trong tham số đầu vào của thủ tục được lưu trữ hoặc đầu vào cho hàm dạng bảng và được sử dụng với bảng trong câu lệnh T-SQL. Có thể có nhiều tình huống hơn cho chiến lược này khi sử dụng T-SQL. Trong khi lập trình với SQL Server, nhà phát triển phải chia chuỗi thành các phần tử bằng dấu tách. Sử dụng hàm tách đối với chuỗi xác định dấu phân cách làm tham số đầu vào. Toàn bộ chuỗi sẽ được tách ra và trả về dưới dạng bảng. Trong một số trường hợp nhất định, tham số đầu vào có thể là sự kết hợp của hai hoặc nhiều giá trị được phân tách bằng ký tự cho mỗi bộ đầu vào. Ví dụ N’203616, 198667, 193718, 188769,…’ N’1021. 203616$1021. 198667$1022. 193718$1022. 188769$…’ N’1021. 203616,198667$1022. 193718,188769$…' Có những lợi thế khi thực thi một luồng với các giá trị được phân tách bằng dấu phẩy so với thực thi đa luồng trong SQL Server
Tách chức năng sử dụng vòng lặpTrong hầu hết các trường hợp, nhà phát triển viết hàm bảng có giá trị bằng một vòng lặp để trích xuất dữ liệu từ các giá trị được phân tách bằng dấu phẩy sang định dạng danh sách hoặc bảng. Một vòng lặp trích xuất từng bộ một với đệ quy. Các vấn đề về hiệu suất có thể phát sinh khi làm việc với tham số đầu vào lớn vì sẽ mất nhiều thời gian hơn để giải nén nó. Ở đây, chúng ta có một hàm đơn giản với tên split_string, hàm này đang sử dụng một vòng lặp bên trong hàm có giá trị bảng. Hàm này cho phép hai đối số hoặc tham số đầu vào (1 – chuỗi đầu vào và 2 – dấu phân cách) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 TẠO CHỨC NĂNG split_string ( @in_string VARCHAR(MAX), @phân cách VARCHAR(1) ) RETURNS @list TABLE(tuple VARCHAR(100)) BẰNG BẮT ĐẦU WHILE LEN(@in_string) > 0 BẮT ĐẦU CHÈN VÀO @danh sách(tuple) CHỌN trái(@in_string, charindex(@delimiter, @in_string+',') -1) as tuple
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), '') kết thúc TRẢ LẠI KẾT THÚC Bảng tập hợp kết quả được hàm này trả về chứa mọi chuỗi con của chuỗi tham số đầu vào được phân tách bằng dấu phân cách. Các chuỗi con trong bảng theo thứ tự xảy ra trong tham số đầu vào. Nếu dấu phân cách hoặc dấu phân cách không phối hợp với bất kỳ phần nào của chuỗi đầu vào, thì bảng đầu ra sẽ chỉ có một thành phần. Hàm dạng bảng sẽ trả về tập kết quả ở định dạng hàng-cột từ chuỗi được phân tách bằng dấu phẩy 1 CHỌN * TỪ split_string('1001,1002,1003,1004', ',') Các phiên bản gần đây của SQL Server cung cấp hàm tích hợp string_split() để thực hiện tác vụ tương tự với các tham số đầu vào của chuỗi đầu vào và dấu phân cách. Nhưng có một cách hiệu quả khác để thực hiện tác vụ này bằng XML Tách chức năng sử dụng XMLHàm Split sử dụng logic XML là một phương pháp hữu ích để tách các giá trị khỏi một chuỗi được phân tách. Sử dụng logic XML với cách tiếp cận XQUERY dựa trên T-SQL dẫn đến T-SQL không được mô tả là đệ quy sử dụng vòng lặp hoặc con trỏ. Về cơ bản, chuỗi tham số đầu vào được chuyển thành XML bằng cách thay thế nút XML trong khớp nối T-SQL. XML kết quả được sử dụng trong XQUERY để trích xuất giá trị nút thành định dạng bảng. Nó cũng dẫn đến hiệu suất tốt hơn Ví dụ: chúng tôi có một giá trị được phân tách bằng dấu phẩy với biến @user_ids trong câu lệnh T-SQL bên dưới. Trong một biến, dấu phẩy được thay thế bằng các thẻ XML để biến nó thành tài liệu kiểu dữ liệu XML và sau đó được trích xuất bằng XQUERY với XPATH là '/root/U' 1 2 3 4 5 6 7 8 9 10 KHAI BÁO @user_ids NVARCHAR(MAX) = N'203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146'
KHAI BÁO @sql_xml XML = Cast('
CHỌN f. x. giá trị('. ', 'LỚN') NHƯ user_id VÀO # người dùng TỪ @sql_xml. nút('/root/U') f(x)
CHỌN * TỪ # người dùng Trong hàm trên, chúng ta đã sử dụng kiểu dữ liệu VARCHAR(100) cho giá trị được trích xuất thay vì BIGINT. Cũng có thể có những thay đổi trong chuỗi tham số đầu vào. Ví dụ: nếu chuỗi đầu vào có thêm một dấu ‘,’ ở đầu hoặc cuối chuỗi, chuỗi đó sẽ trả về một hàng bổ sung có 0 cho kiểu dữ liệu BIGINT và ” cho VARCHAR Ví dụ, 1 2 3 4 5 KHAI BÁO @user_ids VARCHAR(MAX) = N'203616, 198667, 193718, 188769, 183820, 178871,' KHAI BÁO @sql_xml XML = Cast('
CHỌN f. x. giá trị('. ', 'LỚN') NHƯ user_id TỪ @sql_xml. nút('/root/U') f(x) Để xử lý loại dữ liệu ” hoặc 0 này trong tập kết quả, chúng ta có thể thêm điều kiện bên dưới vào câu lệnh T-SQL 1 2 3 4 5 6 KHAI BÁO @user_ids VARCHAR(MAX) = N'203616, 198667, 193718, 188769, 183820, 178871,' KHAI BÁO @sql_xml XML = Cast('
CHỌN f. x. giá trị('. ', 'LỚN') NHƯ user_id TỪ @sql_xml. nút('/root/U') f(x) Ở ĐÂU f. x. giá trị('. ', 'BIGINT') <> 0 Ở đây, f. x. giá trị('. ’, ‘BIGINT’) <> 0 loại trừ 0 và f. x. giá trị('. ’, ‘VARCHAR(100)’) <>” loại trừ dấu ” trong tập kết quả truy vấn. Điều kiện T-SQL này có thể được thêm vào các hàm có giá trị bảng với số tham số đầu vào để xử lý dấu phân cách bổ sung này Hàm số 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 TẠO CHỨC NĂNG split_string_XML ( @in_string VARCHAR(MAX), @phân cách VARCHAR(1) ) RETURNS @list TABLE(tuple VARCHAR(100)) BẰNG BẮT ĐẦU KHAI BÁO @sql_xml XML = Cast('
CHÈN VÀO @danh sách(tuple) CHỌN f. x. giá trị('. ', 'VARCHAR(100)') AS tuple TỪ @sql_xml. nút('/root/U') f(x) Ở ĐÂU f. x. giá trị('. ', 'BIGINT') <> 0
TRẢ LẠI KẾT THÚC Chấp hành 1 2 CHỌN * TỪ split_string_XML('203616,198667,193718,188769,183820,178871,173922,, ',') Tổ hợp các ký tự trong chuỗi được phân tách bằng dấu phân cáchNhưng còn khi tham số đầu vào là sự kết hợp của hai giá trị có nhiều dấu phân cách thì sao? 1 2 3 4 5 6 7 8 9 10 11 12 KHAI BÁO @in_string VARCHAR(MAX) = '1021|203616$1021|198667$1022|193718$1022|188769$' KHAI BÁO @sql_xml XML = Cast('
CHỌN X. Y. giá trị('(U)[1]', 'VARCHAR(20)') AS role_id, X. Y. giá trị('(U)[2]', 'VARCHAR(20)') AS user_id TỪ ( CHỌN Truyền('+ Replace(f.x. giá trị('. ', 'VARCHAR(MAX)'), '|', ')+< '' AS XML) AS xml_ TỪ @sql_xml. nút('/root/U') f(x) Ở ĐÂU f. x. giá trị('. ', 'VARCHAR(MAX)') <> '' )T NGOÀI ÁP DỤNG T. xml_. nút('root') as X(Y) Ví dụ này tương tự như ví dụ trên nhưng chúng tôi đã sử dụng đường ống “. ” làm dấu phân cách với dấu phân cách thứ hai, đô la “$”. Ở đây, tham số đầu vào là sự kết hợp của hai cột, role_id và user_id. Như chúng ta thấy bên dưới, user_id và role_id nằm trong một cột riêng biệt trong tập hợp kết quả bảng Chúng ta có thể sử dụng bất kỳ ký tự nào trong hàm trên, chẳng hạn như một chuỗi được phân tách bằng một ký tự hoặc tổ hợp các chuỗi được phân tách bằng ký tự. Nhà phát triển chỉ cần thay thế ký tự trong các câu lệnh T-SQL ở trên. Thực hiện theo các bước được mô tả tại đây để chuyển đổi bất kỳ chuỗi được phân tách nào thành danh sách. Chỉ cần nhớ rằng hàm XML của SQL Server chấp nhận đầu vào chuỗi và chuỗi đầu vào sẽ được phân tách bằng dấu tách được chỉ định bằng cách sử dụng hàm dạng bảng Làm cách nào để chuyển đổi chuỗi được phân tách bằng dấu phẩy thành danh sách trong SQL?Hàm dạng bảng sẽ trả về tập hợp kết quả ở định dạng hàng-cột từ chuỗi được phân tách bằng dấu phẩy . Các phiên bản gần đây của SQL Server cung cấp chức năng tích hợp sẵn string_split() để thực hiện tác vụ tương tự với các tham số đầu vào của chuỗi đầu vào và dấu phân cách.
Làm cách nào để phân tích chuỗi được phân tách bằng dấu phẩy trong MySQL?Cách tách văn bản được phân tách bằng dấu phẩy (danh sách ID) trong thủ tục lưu trữ MySQL để sử dụng kết quả trong câu lệnh SQL "IN". CHỌN * TỪ bảng WHERE bảng. id IN (splitStringFunction(dấu phẩySeparatedData, ',')); sql. mysql.
Làm cách nào để chuyển đổi một chuỗi thành danh sách trong SQL?MS-SQL Chuyển chuỗi thành danh sách bảng . TẠO CHỨC NĂNG[dbo]. [UF_StrToTable](@String VARCHAR(MAX), @Delimiter CHAR(1)) RETURNS @Temptable TABLE(Kết quả VARCHAR(8000)) BẮT ĐẦU KHAI BÁO @INDEX int, @SLICE VARCHAR(8000) CHỌN @INDEX = 1 NẾU LEN(@String) < 1 HOẶC @String IS NULL trở lại Làm cách nào để nhận các giá trị riêng biệt từ chuỗi được phân tách bằng dấu phẩy trong SQL Server?Giải pháp 1. Có sẵn trong SQL Server 2016 trở lên. -- Sắp xếp các giá trị. CHỌN giá trị TỪ STRING_SPLIT(@temp, ',') ĐẶT HÀNG THEO giá trị; . CHỌN giá trị DISTINCT TỪ STRING_SPLIT(@temp, ','); |
