Làm cách nào để kiểm tra chế độ SQL trong MySQL 8?

Các nhà phát triển MySQL, với mỗi phiên bản mới, cố gắng làm cho MySQL tuân thủ tiêu chuẩn SQL hơn. Vì vậy, khi bạn di chuyển dự án web của mình sang một máy chủ mới, các truy vấn SQL cũ của bạn có thể ngừng hoạt động. Bởi vì phiên bản mới hơn của MySQL không hỗ trợ chúng. Và, nếu bạn không có thời gian [hoặc ngân sách] để viết lại tất cả các truy vấn không tương thích đó, bạn sẽ cần một giải pháp tạm thời nhanh chóng

Giải pháp là đặt Chế độ SQL trong MySQL để làm cho phiên bản MySQL mới hơn tương thích với các truy vấn cũ của bạn. Ngoài ra, hãy thực hiện một số điều chỉnh bổ sung. Giải pháp này được mô tả dưới đây

Tất nhiên, đó là một giải pháp tạm thời. Điều đúng đắn là viết lại các truy vấn SQL của dự án của bạn. Để làm cho chúng tương thích với phiên bản MySQL mới

nội dung

1. 1. Thay đổi cấu hình MySQL 8
2. 2. Ví dụ về Chế độ SQL trong MySQL 8
   1. 2. 1. CHỈ_FULL_GROUP_BY
   2. 2. 2 STRICT_TRANS_TABLES
   3. 2. 3 KHÔNG_ZERO_IN_DATE
   4. 2. 4 KHÔNG_ZERO_DATE
   5. 2. 5 ERROR_FOR_DIVISION_BY_ZERO
   6. 2. 6 KHÔNG_AUTO_CREATE_USER
   7. 2. 7 KHÔNG_ENGINE_SUBSTITUTION
3. Phần kết luận

1. Thay đổi cấu hình MySQL 8

Tất cả các lệnh điều khiển và tên tệp/vị trí bên dưới được cung cấp cho Ubuntu [14. 04 – 22. 04] hệ điều hành

1. Chỉnh sửa tệp cấu hình MySQL

default_authentication_plugin=mysql_native_password

2

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

Đối với các hệ thống lớn, tôi thường tăng sort_buffer_size. Vì vậy, tôi thêm vào cuối

default_authentication_plugin=mysql_native_password

2

sort_buffer_size = 512K

Bạn có thể cần áp dụng những thay đổi sau trong

default_authentication_plugin=mysql_native_password

2

Làm cho MySQL 8 tương thích với mật khẩu cũ

Nếu mật khẩu người dùng MySQL cũ của bạn không hoạt động, hãy thêm vào cuối

default_authentication_plugin=mysql_native_password

2

default_authentication_plugin=mysql_native_password

và chạy cho mật khẩu cũ

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

Thay thế

default_authentication_plugin=mysql_native_password

6 và

default_authentication_plugin=mysql_native_password

7 bằng các giá trị bạn cần.
[nguồn]

Trường hợp Cảnh cáo. mysql_connect[]. Máy chủ đã gửi bộ ký tự [255] không xác định cho máy khách

Nếu bạn có một dự án cũ với tất cả các bảng e. g. trong

default_authentication_plugin=mysql_native_password

8, bạn có thể thêm vào cuối

default_authentication_plugin=mysql_native_password

2

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

2

Nếu cơ sở dữ liệu ở dạng UTF-8, hãy thay thế

default_authentication_plugin=mysql_native_password

8 bằng

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

1.
[nguồn]

Nếu bạn thực hiện bất kỳ thay đổi nào trong

default_authentication_plugin=mysql_native_password

2, hãy khởi động lại dịch vụ

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

3

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

7

2. Đặt các chế độ SQL để làm cho các truy vấn của bạn tương thích với phiên bản MySQL mới

Trước tiên, hãy kiểm tra xem các chế độ SQL nào hiện đang được đặt

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

8

Đầu ra sẽ là

Trong MySQL5. 7

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

9

Trong Mysql 8

default_authentication_plugin=mysql_native_password

0

Các hằng số này được giải thích dưới đây. Bạn chỉ cần xóa các hằng số khiến dự án không tương thích với MySQL 8

E. g. chỉnh sửa tệp

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

4 và bên dưới phần

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

5 thêm dòng

default_authentication_plugin=mysql_native_password

3

Bạn cũng có thể đọc bài viết này

Hoặc, với mục đích thử nghiệm, bạn có thể tạm thời [nó sẽ không tồn tại sau khi khởi động lại máy chủ MySQL] đặt chế độ SQL trên toàn cầu [= cho tất cả các máy khách MySQL] là

sort_buffer_size = 512K

0

hoặc chỉ dành cho máy khách MySQL hiện tại

sort_buffer_size = 512K

1

[nguồn]

2 Ví dụ về Chế độ SQL trong MySQL 8

từ chối trách nhiệm. Tất cả các liên kết trong phần này đều dẫn đến tài liệu MySQL chính thức. Tôi chỉ đưa ra một số ví dụ về các truy vấn SQL trong đó việc đặt các chế độ SQL sẽ dẫn đến lỗi. Mô tả về các chế độ SQL trong phần này là cơ bản nhất. Nó không chi tiết cũng không đầy đủ. Để biết mô tả đầy đủ, vui lòng nhấp vào tên chế độ SQL bên dưới và đọc tài liệu chính thức của MySQL

2. 1. CHỈ_FULL_GROUP_BY

CHỈ_FULL_GROUP_BY – khi được đặt, MySQL buộc chúng tôi phải nhóm trên tất cả các cột không tổng hợp [theo SQL 92] được sử dụng trong SELECT, ORDER BY hoặc HAVING. Mặc dù việc nhóm theo các cột phụ thuộc chức năng là không cần thiết [theo SQL. 1999]

E. g. các truy vấn

sort_buffer_size = 512K

2

sẽ sản xuất

LỖI 1055 [42000]. Biểu thức #2 của danh sách CHỌN không có trong mệnh đề GROUP BY và chứa cột không tổng hợp 'wpdiaries. wp_posts. post_status' không phụ thuộc chức năng vào các cột trong mệnh đề GROUP BY;

bởi vì

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

6 không phải là khóa duy nhất hoặc khóa chính. Và

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

7 không phụ thuộc chức năng vào nó

Nếu chúng tôi nhóm theo

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

8 ở đây thay vì

mysql
ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

6, lỗi sẽ không phát sinh. Bởi vì tất cả các cột trong bảng ________ 120 phụ thuộc chức năng vào ________ 78. Và SQL. 1999 sẽ cho phép chúng tôi nhóm như vậy

2. 2 STRICT_TRANS_TABLES

STRICT_TRANS_TABLES – đặt Chế độ SQL nghiêm ngặt [đồng thời, xem STRICT_ALL_TABLES]. Chế độ nghiêm ngặt chỉ ảnh hưởng đến các truy vấn thay đổi dữ liệu. e. g. CHÈN hoặc CẬP NHẬT. Nếu không có chế độ này, MySQL sẽ chèn/cập nhật một giá trị liền kề và đưa ra cảnh báo cho chúng tôi

Nếu bạn cố gắng đặt chế độ này một mình, như

sort_buffer_size = 512K

3

, cảnh báo sẽ được tạo ra

Cảnh báo 3135. Các chế độ sql ‘NO_ZERO_DATE’, ‘NO_ZERO_IN_DATE’ và ‘ERROR_FOR_DIVISION_BY_ZERO’ nên được sử dụng với chế độ nghiêm ngặt. Chúng sẽ được hợp nhất với chế độ nghiêm ngặt trong bản phát hành trong tương lai

Bạn có thể xem các cảnh báo bằng lệnh HIỂN THỊ CẢNH BÁO

sort_buffer_size = 512K

4

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

22 ở đây không phải là một phần của câu lệnh SQL. Nó yêu cầu máy khách MySQL hiển thị kết quả theo hàng thay vì theo cột. Nếu bạn đã sử dụng nửa cột

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

23 [thay vì

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

22] ở cuối câu lệnh, kết quả sẽ được hiển thị trong đầu ra của cột

Ngoài ra, vui lòng xem phần So sánh Từ khóa BỎ QUA và Chế độ SQL nghiêm ngặt của tài liệu MySQL

2. 3 KHÔNG_ZERO_IN_DATE

NO_ZERO_IN_DATE – nếu được đặt, không cho phép số không tháng hoặc số không ngày trong những ngày mà năm khác không [e. g.

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

25 hoặc

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

26]

E. g. các truy vấn

sort_buffer_size = 512K

5

sẽ sản xuất

LỖI 1292 [22007]. Giá trị ngày giờ không chính xác. ‘2021-01-00 00. 00. 00' cho cột 'post_date' ở hàng 1

bởi vì chúng tôi đã sử dụng một ngày không trong ngày

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

27

NO_ZERO_IN_DATE không ảnh hưởng đến các ngày như

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

28. Chúng có thể được phép/không được phép trước NO_ZERO_DATE

2. 4 KHÔNG_ZERO_DATE

NO_ZERO_DATE – cấm ngày 0

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

28. Không dùng nữa [sẽ là một phần của Chế độ SQL nghiêm ngặt trong phiên bản tương lai của MySQL]

E. g. các truy vấn

sort_buffer_size = 512K

6

sẽ sản xuất

LỖI 1292 [22007]. Giá trị ngày giờ không chính xác. ‘0000-00-00’ cho cột ‘post_date’ ở hàng 1

bởi vì chúng tôi đã cố gắng đặt giá trị '

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

70'. Lỗi tương tự phát sinh nếu chúng tôi cố gắng chèn

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

28 vào trường NGÀY hoặc NGÀY

Nếu NO_ZERO_DATE được đặt, nhưng STRICT_TRANS_TABLES không được đặt, các truy vấn INSERT sẽ chèn

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

28 và các truy vấn CẬP NHẬT sẽ cập nhật thành

cd /etc/mysql/mysql.conf.d
sudo nano mysqld.cnf

28 là được. Nhưng cảnh báo 1264 Giá trị ngoài phạm vi cho cột 'post_date' ở hàng 1 sẽ được tạo

2. 5 ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO – không cho phép chia cho 0 trong truy vấn INSERT và UPDATE. Chỉ hoạt động cùng với chế độ nghiêm ngặt. Không dùng nữa [sẽ là một phần của Chế độ SQL nghiêm ngặt trong phiên bản tương lai của MySQL]

E. g. các truy vấn

sort_buffer_size = 512K

7

sẽ sản xuất

LỖI 1365 [22012]. chia cho 0

Lỗi tương tự sẽ được tạo ra nếu chúng ta cố CHÈN một giá trị có phép chia cho 0

Nếu ERROR_FOR_DIVISION_BY_ZERO được đặt, nhưng STRICT_TRANS_TABLES không được đặt

• truy vấn INSERT sẽ cố gắng chèn NULL và tạo cảnh báo 1365 Chia cho 0. Nếu cột được khai báo là NOT NULL, lỗi 1048 [23000] Column cannot be null sẽ được tạo ra
• một truy vấn CẬP NHẬT sẽ cố gắng đặt giá trị trường thành NULL và tạo cảnh báo 1365 Chia cho 0. Nếu cột của chúng tôi được khai báo là KHÔNG NULL, giá trị sẽ được đặt thành 0 cho các cột số và chuỗi rỗng cho các cột loại ký tự. Và một cảnh báo bổ sung 1048 Cột không thể rỗng sẽ được tạo

Nếu STRICT_TRANS_TABLES được đặt, nhưng ERROR_FOR_DIVISION_BY_ZERO không được đặt

• truy vấn INSERT sẽ cố gắng chèn NULL – không có cảnh báo. Nếu cột được khai báo là NOT NULL, lỗi 1048 [23000] Column cannot be null sẽ được tạo ra
• một truy vấn CẬP NHẬT sẽ thử để đặt giá trị trường thành NULL – không có cảnh báo. Nếu cột của chúng tôi được khai báo là KHÔNG NULL, lỗi 1048 [23000] Cột không thể null sẽ được tạo.

Đối với các truy vấn CHỌN, trong trường hợp chia cho 0, NULL luôn được trả về, bất kể chế độ nào được đặt. e. g. truy vấn

sort_buffer_size = 512K

8

sẽ luôn trả về NULL. Nhưng nếu cờ ERROR_FOR_DIVISION_BY_ZERO được đặt, cảnh báo 1365 Chia cho 0 cũng sẽ được tạo

2. 6 KHÔNG_AUTO_CREATE_USER

NO_AUTO_CREATE_USER – đã bị xóa trong MySQL 8. 0. 11. Ngăn GRANT tự động tạo người dùng MySQL. Vì GRANT không thể tạo người dùng trong các phiên bản MySQL 8 gần đây, SQL này không khả dụng nữa. Và chúng tôi không đưa ra bất kỳ ví dụ nào ở đây

Tuy nhiên, như một lưu ý nhỏ, nếu bạn cố chạy lệnh GRANT trong MySQL 8 như

sort_buffer_size = 512K

9

, bạn sẽ gặp lỗi

LỖI 1064 [42000]. Bạn có lỗi trong cú pháp SQL của mình;

Bạn cần tạo người dùng trước. Và chỉ sau đó chạy lệnh GRANT

default_authentication_plugin=mysql_native_password

0

2. 7 KHÔNG_ENGINE_SUBSTITUTION

NO_ENGINE_SUBSTITUTION – đưa ra lỗi khi CREATE TABLE hoặc ALTER TABLE chứa công cụ lưu trữ không tồn tại

E. g. các truy vấn

default_authentication_plugin=mysql_native_password

1

sẽ sản xuất

LỖI 1286 [42000]. Công cụ lưu trữ không xác định 'MyUknownEngine'

bởi vì công cụ lưu trữ 'MyUknownEngine' không tồn tại

Nếu không có NO_ENGINE_SUBSTITUTION, bảng sẽ được tạo bằng công cụ lưu trữ mặc định. Và một cảnh báo sẽ được tạo ra

Phần kết luận

Cảm ơn bạn đã đọc đến cuối

Có nhiều trường hợp điều này có thể hữu ích

Ví dụ: bạn có thể gặp vấn đề về hiệu suất với dự án của mình. Và bạn quyết định mở rộng dự án của mình theo chiều ngang [bằng cách thêm máy chủ mới]. Thông thường, nút cổ chai hiệu suất dự án là các truy vấn SQL. Các truy vấn SQL thường chậm hơn nhiều so với CMS hoặc mã PHP khung của bạn. Trong trường hợp này, bạn có thể thêm nhiều máy chủ MySQL hơn bằng cách sao chép. Và các máy chủ sao chép có thể có nhiều phiên bản MySQL mới hơn. Trong trường hợp này, bạn cần làm cho các phiên bản MySQL mới tương thích với phiên bản cũ

Hoặc, nếu bạn là nhà phát triển web, bạn có thể cần triển khai một số dự án cũ trên máy ảo VirtualBox để phát triển web cục bộ. Trong trường hợp này, nếu bạn có phiên bản MySQL gần đây trên máy ảo của mình. Và bạn có thể cần làm cho nó tương thích với các truy vấn SQL cũ của dự án của bạn

Tất nhiên, có thể có các giải pháp khác nhau

Ví dụ: bạn có thể thiết lập dự án của mình bằng bộ chứa Docker. Trong trường hợp này, việc thay đổi phiên bản MySQL [và thử nghiệm với các phiên bản MySQL khác nhau] sẽ thực sự nhanh chóng. Nếu bạn quyết định sử dụng Docker và nếu dự án của bạn nằm trong WordPress, bạn có thể thấy bài viết này về cách thêm XDebug vào hình ảnh chính thức của WordPress hữu ích

Làm cách nào để kiểm tra chế độ SQL MySQL?

Chế độ SQL mặc định trong MySQL là gì?

Làm cách nào để kiểm tra phiên bản MySQL?

Làm cách nào để đặt sql_mode trong CNF của tôi?