Một trong những chương trình truyền hình yêu thích của tôi khi còn là một cậu bé là Tìm kiếm… do cố lãnh đạo vĩ đại Leonard Nimoy tổ chức. Mỗi tuần anh ấy sẽ khám phá một số hiện tượng tự nhiên bất thường hoặc sự kiện huyền bí. Nó chưa bao giờ thất bại trong việc khơi dậy ngọn lửa trí tưởng tượng của tôi. Tua nhanh đến năm 2016, và bây giờ tôi thấy mình đang thực hiện một nhiệm vụ của riêng mình. Mặc dù MySQL có tính năng khớp Biểu thức chính quy thông qua toán tử REGEXP nhưng nó không cung cấp hàm tương đương với hàm REGEXP_REPLACE của Oracle, điều này càng bí ẩn hơn khi bạn cho rằng Oracle sở hữu MySQL. Tham gia cùng tôi khi chúng tôi cố gắng giải thích sự không phù hợp này giữa hai DBMS và tìm kiếm các thay thế do người dùng xác định
Nguồn gốc và công dụng
Các chức năng thay thế Regex có thể được truy nguyên từ tiện ích sed UNIX. Được phát triển vào năm 1973/74, nó đã phân tích cú pháp và chuyển đổi văn bản, sử dụng tìm kiếm được định dạng là “s/regexp/replacement/”. Chúng mở rộng chức năng của các hàm REPLACE thông thường bằng cách cho phép bạn tìm kiếm theo mẫu Regex. Ngoài ra, các triển khai khác nhau có tất cả các loại tính năng chuyên dụng, chẳng hạn như khả năng thay thế một hoặc tất cả các kết quả trùng khớp, bắt đầu khớp ở đâu, v.v...
Hãy sử dụng chức năng REGEXP_REPLACE của Oracle làm hướng dẫn. Nó chấp nhận các tham số sau
REGEXP_REPLACE[ string, pattern [, replacement_string [, start_position [, th_appearance [, match_parameter ] ] ] ] ]
- chuỗi. Chuỗi để tìm kiếm. Nó có thể là CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB hoặc NCLOB
- mẫu. Thông tin phù hợp với biểu thức chính quy
- chuỗi thay thế. Không bắt buộc. Các mẫu phù hợp sẽ được thay thế bằng chuỗi thay thế trong chuỗi. Nếu tham số replace_string bị bỏ qua, hàm chỉ cần xóa tất cả các mẫu phù hợp và trả về chuỗi kết quả
- vị trí bắt đầu. Không bắt buộc. Đó là vị trí trong chuỗi nơi bắt đầu tìm kiếm. Nếu bỏ qua, nó mặc định là 1, vị trí đầu tiên trong chuỗi
- nth_appearance. Không bắt buộc. Đây là lần xuất hiện thứ n của mẫu trong chuỗi. Nếu bỏ qua, nó sẽ mặc định là 1, đây là lần xuất hiện đầu tiên của mẫu trong chuỗi. Nếu bạn chỉ định 0 cho tham số này, tất cả các lần xuất hiện của mẫu sẽ được thay thế bằng chuỗi
- match_parameter. Không bắt buộc. Nó cho phép bạn sửa đổi hành vi khớp cho hàm REGEXP_REPLACE. Nó có thể là sự kết hợp của những điều sau đây
- 'c'. Thực hiện khớp phân biệt chữ hoa chữ thường
- 'tôi'. Thực hiện khớp không phân biệt chữ hoa chữ thường
- 'N'. Cho phép ký tự dấu chấm [. ] để khớp với ký tự xuống dòng. Theo mặc định, khoảng thời gian là ký tự đại diện
- 'tôi'. biểu thức được giả định là có nhiều dòng. Theo mặc định, biểu thức được coi là một dòng
- 'x'. Các ký tự khoảng trắng bị bỏ qua. Theo mặc định, các ký tự khoảng trắng được khớp giống như bất kỳ ký tự nào khác.
Đối với việc sắp đưa nó vào MySQL, đã có một yêu cầu tính năng từ năm 2007. Không có xúc xắc cho đến nay…
Kiểm tra các chức năng của ứng viên
Tôi đã áp dụng các chức năng người dùng ứng viên đối với cơ sở dữ liệu mẫu Sakila mã nguồn mở. Nó được phát triển bởi Mike Hillyer, cựu thành viên của nhóm tài liệu MySQL AB và được tạo riêng cho mục đích cung cấp một lược đồ tiêu chuẩn để sử dụng trong sách, hướng dẫn, bài báo, v.v. Tôi đã mô tả các bước để tải xuống và cài đặt nó trong bài viết Tạo báo cáo về dữ liệu MySQL của mình. Hãy xem nó nếu bạn muốn tự cài đặt nó
Hàm regex_replace[] của Rasika Godawatte
Kỹ sư phần mềm Rasika Godawatte cần một hàm regex_replace nên anh ấy đã tự viết một hàm. Anh ấy chia sẻ kết quả trên blog của mình
Trong MySQL5. 0. 1 trở lên, bạn phải BẬT chế độ NO_BACKSLASH_ESCAPES, trước khi bạn có thể sử dụng chức năng trên để thay thế bất kỳ ký tự nào được thoát bằng dấu gạch chéo ngược “”, i. e. A, B, v.v…
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
Đây là mã nguồn chức năng
DELIMITER $$ CREATE FUNCTION `regex_replace`[pattern VARCHAR[1000],replacement VARCHAR[1000],original VARCHAR[1000]] RETURNS VARCHAR[1000] DETERMINISTIC BEGIN DECLARE temp VARCHAR[1000]; DECLARE ch VARCHAR[1]; DECLARE i INT; SET i = 1; SET temp = ''; IF original REGEXP pattern THEN loop_label: LOOP IF i>CHAR_LENGTH[original] THEN LEAVE loop_label; END IF; SET ch = SUBSTRING[original,i,1]; IF NOT ch REGEXP pattern THEN SET temp = CONCAT[temp,ch]; ELSE SET temp = CONCAT[temp,replacement]; END IF; SET i=i+1; END LOOP; ELSE SET temp = original; END IF; RETURN temp; END$$ DELIMITER ;
Chúng ta có thể áp dụng hàm của Rasika cho cơ sở dữ liệu Sakila để thay thế tất cả các ký tự không phải chữ và số [không bao gồm khoảng trắng] bằng truy vấn sau
SELECT description as 'before', regex_replace[[^A-Za-z0-9 ]', '[[MATCH]]', description] as 'after' FROM sakila.film where description REGEXP '[^A-Za-z0-9 ]';
Từ ba hàng đầu tiên được trả về, chúng ta có thể thấy rằng các dấu gạch nối [-] đã được khớp
Before After --------------------------------------------------------------------------------------------------------------- A Fast-Paced Documentary of a Pastry Chef And a... | A Fast[[MATCH]]Paced Documentary of a Pastry Chef And a... A Action-Packed Tale of a Man And a Lumberjack... | A Action[[MATCH]]Packed Tale of a Man And a Lumberjack... A Action-Packed Drama of a Dentist And a Croc... | A Action[[MATCH]]Packed Drama of a Dentist And a Croc... ...
Các toán tử Regex khác như bắt đầu chuỗi [^], kết thúc chuỗi [$] và các bộ định lượng, bao gồm 0 hoặc một [?], một hoặc nhiều [+] và 0 hoặc nhiều [*], cũng được phép
Hạn chế chính – và đó là một hạn chế lớn – là do hàm REGEX không trả về chuỗi đã khớp, nên không thể biết chính xác chuỗi con nào đã khớp. Vì thế,
bất kỳ chức năng regex_replace[] DIY nào cũng phải tự giới hạn ở các mẫu ký tự đơn.
Tôi đã thử một vài biến thể khác của chức năng trên, nhưng tất cả chúng đều hoạt động khá giống nhau
Tùy chọn khác. Hàm UDF
Nếu bạn thực sự bị ràng buộc và quyết tâm tính gần đúng hàm REGEXP_REPLACE của Oracle, thì tùy chọn tốt nhất của bạn là Hàm do người dùng xác định [UDF]. UDF là một hàm được biên dịch hoạt động giống như một hàm MySQL gốc. UDF thường được viết bằng C hoặc C++ và được thêm vào MySQL bằng lệnh CREATE FUNCTION
Cũng giống như các hàm regex_replace[] do người dùng không biên dịch, bạn không cần phải tự viết một hàm vì có rất nhiều lựa chọn tuyệt vời có thể tìm thấy trực tuyến. Đây là một cặp vợ chồng
- Arnold Daniels’ lib_mysqludf_preg là một thư viện MySQL UDF cung cấp quyền truy cập vào thư viện PCRE [perl compatible-regular-expressions] để so khớp mẫu. Thư viện PCRE là một tập hợp các hàm thực hiện khớp mẫu biểu thức chính quy bằng cách sử dụng cùng cú pháp và ngữ nghĩa như Perl 5. Cú pháp này thường có thể xử lý các biểu thức và nắm bắt phức tạp hơn so với các triển khai biểu thức chính quy tiêu chuẩn
- Thư viện mysql-udf-regexp của Hartmut Holzgraefe được đề cập như một giải pháp thay thế trên trang tính năng MySQL. Nó trông giống thư viện của Arnold Daniels một cách đáng ngờ. Không chắc sự khác biệt là gì
Phần kết luận
Ở phần đầu của bài viết này, chúng tôi đã bắt tay vào tìm kiếm các hàm thay thế biểu thức chính quy do người dùng xác định. Những gì chúng tôi nhận thấy là bạn sẽ phải giới hạn phạm vi của mình ở các mẫu ký tự đơn trừ khi bạn sẵn sàng dành thêm nỗ lực để biên dịch và cài đặt UDF.