MySQL có phân biệt chữ hoa chữ thường trong các truy vấn không?
Cách tiêu chuẩn để thực hiện các truy vấn không phân biệt chữ hoa chữ thường trong SQL là sử dụng các hàm trên hoặc dưới của SQL như sau Show
HOẶC LÀ
Phương pháp này là đặt trường bạn đang tìm kiếm thành chữ hoa hoặc chữ thường, sau đó cũng đặt chuỗi tìm kiếm thành chữ hoa hoặc chữ thường theo hàm SQL So sánh chuỗi KHÔNG phân biệt chữ hoa chữ thường theo mặc định trong MySQL, nhưng nếu có nhu cầu so sánh chuỗi phân biệt chữ hoa chữ thường, có nhiều cách để giải quyết vấn đề này 1. Sử dụng toán tử BINARY Toán tử BINARY chuyển đổi một chuỗi ký tự thành chuỗi nhị phân. Nó buộc thực hiện so sánh chuỗi ký tự theo từng byte bằng cách sử dụng các giá trị byte số thay vì từng ký tự Ví dụ: truy vấn sau đây trả về 1 viết tắt của TRUE vì chuỗi geek giống với chuỗi GEEK SELECT 'geek' = 'GEEK'; Nhưng nếu chúng ta áp dụng toán tử BINARY cho chuỗi 'GEEK', thì kết quả là FALSE (trả về 0) vì chuỗi chữ thường geek khác với chuỗi chữ hoa GEEK SELECT 'geek' = BINARY 'GEEK'; Một vi dụ khac select 'géek' = 'geek'; Truy vấn trên trả về 1 là TRUE, trong khi select 'geek' = BINARY 'geek';trả về 0 là FALSE Truy vấn sau đây là tìm kiếm phân biệt chữ hoa chữ thường đối với một cột bằng cách sử dụng toán tử BINARY. Nó trích xuất tất cả các bản ghi danh mục trong đó tên danh mục chứa ký tự chữ thường 's'. Lưu ý rằng danh mục 'Hải sản' không có trong kết quả vì chữ cái đầu tiên 'S' là chữ hoa select CategoryID, CategoryName from categories where BINARY CategoryName like '%s%'; 5 bản ghi được trả về Dưới đây là phiên bản không phân biệt chữ hoa chữ thường của truy vấn trên và nó trích xuất tất cả các bản ghi danh mục trong đó tên danh mục chứa ký tự 's' hoặc 'S' (cả chữ thường 's' và chữ hoa 'S' đều được trả về trong kết quả) select CategoryID, CategoryName from categories where CategoryName like '%s%'; 6 bản ghi được trả lại Một vài điều cần lưu ý về việc sử dụng toán tử BINARY
2. Sử dụng toán tử COLLATE để xác định một cột là phân biệt chữ hoa chữ thường Bên dưới, một bảng mới được tạo và cột CategoryName được xác định bởi COLLATE utf8mb4_bin phân biệt chữ hoa chữ thường SELECT 'geek' = BINARY 'GEEK';0 Bây giờ thêm một số bản ghi vào bảng mới SELECT 'geek' = BINARY 'GEEK';1 Truy vấn bảng mới như bình thường (không cần toán tử BINARY và tìm kiếm trên cột CategoryName phân biệt chữ hoa chữ thường theo mặc định) Bỏ qua trường hợp trong mệnh đề 5 rất đơn giản. Ví dụ: bạn có thể chuyển đổi cả hai vế của phép so sánh sang ký hiệu viết hoa tất cả
Bất kể cách viết hoa được sử dụng cho cụm từ tìm kiếm hoặc cột 6, hàm 7 làm cho chúng khớp như mong muốnGhi chúMột cách khác để so khớp không phân biệt chữ hoa chữ thường là sử dụng một “đối chiếu” khác. Các đối chiếu mặc định được sử dụng bởi SQL Server và MySQL không phân biệt chữ hoa và chữ thường—chúng không phân biệt chữ hoa chữ thường theo mặc định Logic của truy vấn này là hoàn toàn hợp lý nhưng kế hoạch thực hiện thì không DB2 Oracle PostgreSQL
Đó là sự trở lại của người bạn cũ của chúng tôi khi quét toàn bộ bảng. Mặc dù có một chỉ mục trên 6, nhưng nó không sử dụng được—vì tìm kiếm không phải trên 6 mà trên 0. Từ quan điểm của cơ sở dữ liệu, đó là một cái gì đó hoàn toàn khácThay mặt tôiTôi kiếm sống từ việc đào tạo SQL, điều chỉnh và tư vấn SQL và cuốn sách của tôi “Giải thích về hiệu suất SQL”. Tìm hiểu thêm tại https. // winand. tại/ Đây là một cái bẫy mà tất cả chúng ta có thể rơi vào. Chúng tôi nhận ra mối quan hệ giữa 6 và 0 ngay lập tức và mong muốn cơ sở dữ liệu cũng “thấy” mối quan hệ đó. Trong thực tế, chế độ xem của trình tối ưu hóa giống như thế này
Hàm 7 chỉ là một hộp đen. Các tham số của hàm không liên quan vì không có mối quan hệ chung giữa các tham số của hàm và kết quảMẹoThay thế tên hàm bằng 4 để hiểu quan điểm của trình tối ưu hóaĐánh giá thời gian biên dịchTrình tối ưu hóa có thể đánh giá biểu thức ở phía bên tay phải trong “thời gian biên dịch” vì nó có tất cả các tham số đầu vào. Do đó, kế hoạch thực thi của Oracle (phần “Thông tin vị ngữ”) chỉ hiển thị ký hiệu chữ hoa của cụm từ tìm kiếm. Hành vi này rất giống với trình biên dịch đánh giá các biểu thức không đổi tại thời điểm biên dịch Để hỗ trợ truy vấn đó, chúng tôi cần một chỉ mục bao gồm cụm từ tìm kiếm thực tế. Điều đó có nghĩa là chúng tôi không cần chỉ mục trên 6 nhưng trên 0
Chỉ mục có định nghĩa chứa các hàm hoặc biểu thức được gọi là chỉ mục dựa trên hàm (FBI). Thay vì sao chép dữ liệu cột trực tiếp vào chỉ mục, chỉ mục dựa trên hàm sẽ áp dụng hàm trước và đưa kết quả vào chỉ mục. Kết quả là, chỉ mục lưu trữ tên trong tất cả các ký hiệu viết hoa Cơ sở dữ liệu có thể sử dụng chỉ mục dựa trên hàm nếu biểu thức chính xác của định nghĩa chỉ mục xuất hiện trong câu lệnh SQL—như trong ví dụ trên. Kế hoạch thực hiện xác nhận điều này DB2____30_______ Truy vấn đã được thay đổi thành 7 (không có 7 ở phía bên tay phải) để nhận được kết quả như mong đợi. Khi sử dụng 9, trình tối ưu hóa thực hiện ước tính sai tổng thể và mong đợi 4% số hàng trong bảng được chọn. Điều này khiến trình tối ưu hóa bỏ qua chỉ mục và thực hiện 0. Xem để biết tại sao điều đó có thể có ý nghĩaOracle PostgreSQL
Đó là một 1 thông thường như được mô tả trong Chương 1. Cơ sở dữ liệu đi qua cây B và theo chuỗi nút lá. Không có hoạt động hoặc từ khóa chuyên dụng cho các chỉ mục dựa trên chức năngCảnh báoĐôi khi các công cụ ORM sử dụng 7 và 3 mà nhà phát triển không biết. Ngủ đông, ví dụ, cho các tìm kiếm không phân biệt chữ hoa chữ thườngKế hoạch thực hiện vẫn chưa giống như trong phần trước nếu không có 7; . Điều đặc biệt kỳ lạ là trình tối ưu hóa mong đợi tìm nạp nhiều hàng hơn từ bảng so với 1 cung cấp ngay từ đầu. Làm cách nào nó có thể tìm nạp 100 hàng từ bảng nếu lần quét chỉ mục trước đó chỉ trả về 40 hàng? . Các ước tính mâu thuẫn như thế này thường chỉ ra các vấn đề với số liệu thống kê. Trong trường hợp cụ thể này là do cơ sở dữ liệu Oracle không cập nhật số liệu thống kê của bảng khi tạo chỉ mục mới (xem thêm)Thống kê Oracle cho các chỉ mục dựa trên chức năngCơ sở dữ liệu Oracle duy trì thông tin về số lượng giá trị cột riêng biệt như một phần của bảng thống kê. Những số liệu này được sử dụng lại nếu một cột là một phần của nhiều chỉ mục Thống kê cho chỉ mục dựa trên chức năng (FBI) cũng được lưu giữ ở cấp độ bảng dưới dạng cột ảo. Mặc dù cơ sở dữ liệu Oracle tự động thu thập số liệu thống kê chỉ mục cho các chỉ mục mới (), nhưng nó không cập nhật số liệu thống kê bảng. Vì lý do này, tài liệu Oracle khuyên bạn nên cập nhật số liệu thống kê bảng sau khi tạo chỉ mục dựa trên chức năng
Khuyến nghị cá nhân của tôi thậm chí còn đi xa hơn. sau mỗi lần thay đổi chỉ mục, hãy cập nhật số liệu thống kê cho bảng cơ sở và tất cả các chỉ mục của nó. Tuy nhiên, điều đó cũng có thể dẫn đến các tác dụng phụ không mong muốn. Phối hợp hoạt động này với quản trị viên cơ sở dữ liệu (DBA) và tạo bản sao lưu của số liệu thống kê ban đầu Sau khi cập nhật số liệu thống kê, trình tối ưu hóa sẽ tính toán các ước tính chính xác hơn Oracle PostgreSQL 0Vì ước tính số lượng hàng đã giảm—từ 50 trong ví dụ trên xuống còn 1 trong kế hoạch thực hiện này—công cụ lập kế hoạch truy vấn thích sử dụng thao tác 8 đơn giản hơnGhi chúđã được giới thiệu với bản phát hành Oracle 11g Mặc dù số liệu thống kê được cập nhật không cải thiện hiệu suất thực thi trong trường hợp này—dù sao thì chỉ mục cũng được sử dụng đúng cách—bạn luôn nên kiểm tra các ước tính của trình tối ưu hóa. Số hàng được xử lý cho mỗi thao tác (ước tính cardinality) là một con số đặc biệt quan trọng cũng được hiển thị trong các kế hoạch thực thi của SQL Server và PostgreSQL MẹoPhụ lục A, "Kế hoạch thực hiện", mô tả ước tính số lượng hàng trong kế hoạch thực hiện của các cơ sở dữ liệu khác SQL Server và MySQL không hỗ trợ các chỉ mục dựa trên chức năng như được mô tả nhưng cả hai đều cung cấp giải pháp thay thế thông qua các cột được tính toán hoặc tạo. Để tận dụng điều này, trước tiên bạn phải thêm một cột được tạo vào bảng có thể được lập chỉ mục sau đó mysql Kể từ MySQL 5. 7 bạn như sau 1 2SQL Server 3 4SQL Server và MySQL có thể sử dụng chỉ mục này bất cứ khi nào biểu thức được lập chỉ mục xuất hiện trong câu lệnh. Trong một số trường hợp đơn giản, SQL Server và MySQL có thể sử dụng chỉ mục này ngay cả khi truy vấn không thay đổi. Tuy nhiên, đôi khi, truy vấn phải được thay đổi để chỉ tên của các cột mới để sử dụng chỉ mục. Luôn kiểm tra kế hoạch thực hiện trong trường hợp nghi ngờ Các truy vấn SQL có phải là trường hợp khôngCác từ khóa SQL (SELECT, FROM, WHERE, v.v. ) không phân biệt chữ hoa chữ thường , nhưng chúng thường được viết hoa tất cả. Tên bảng và cột phân biệt chữ hoa chữ thường trong một số cài đặt. MySQL cung cấp một cài đặt cho phép bạn bật hoặc tắt nó.
bạn sẽ làm một trường hợpNếu bạn muốn phân biệt chữ hoa chữ thường, bạn cần phải sử dụng UPPER() hoặc LOWER() .
Một truy vấn có thể được viết trong bất kỳ trường hợp nào trong MySQL không?Các đối chiếu mặc định được SQL Server và MySQL sử dụng không phân biệt chữ hoa và chữ thường— chúng không phân biệt chữ hoa chữ thường theo mặc định . Logic của truy vấn này là hoàn toàn hợp lý nhưng kế hoạch thực hiện thì không. DB2.
trường hợp nào sau đâyCột chuỗi có phân biệt chữ hoa chữ thường hay không tùy thuộc vào loại cột. Theo mặc định, các loại CHAR và VARCHAR không phân biệt chữ hoa chữ thường, nhưng có thể được khai báo là BINARY để làm cho chúng phân biệt chữ hoa chữ thường. Các cột ENUM , SET và TEXT không phân biệt chữ hoa chữ thường. |