Giới thiệu MySQL

Tình huống mở đầu

Bạn nhận dự án ShopBook, một tiệm sách online có 2.000 đầu sách và khoảng 300 đơn mỗi ngày. Sếp nhắn đúng một dòng: "Em dựng giúp anh MySQL, khoảng 10 phút là xong nhé." Bạn cài xong, tạo bảng books trong ba phút, và tưởng mọi thứ ổn.

Chiều hôm sau, một khách người Nhật đăng ký tài khoản với tên có chữ Hán hiếm là 𠮷田 (Yoshida) và hệ thống báo lỗi. Cùng lúc, kế toán hỏi vì sao một đơn hàng đã hủy vẫn còn dòng trong bảng nhật ký. Bạn chưa hề khai báo "kiểu chữ" hay "cách lưu" nào cho bảng, vậy ai đã chọn thay bạn?

Hãy nghĩ trước một con số: bạn đoán MySQL đã tự quyết định giúp bạn bao nhiêu thứ khi bạn gõ CREATE TABLE? Cuối bài mình sẽ đếm lại cùng bạn.

Ôn nhanh bài trước: ở chương 6, SQL Injection xảy ra khi nào?

Đáp án: khi dữ liệu do người dùng nhập bị nối thẳng vào câu lệnh SQL nên biến thành một phần cú pháp. Chương này bạn sẽ gặp lại chuyện đó khi kết nối từ ứng dụng.

Mục tiêu bài học

Sau bài này bạn có thể:

• Vẽ lại đường đi của một câu lệnh từ client đến mysqld, qua tầng SQL rồi đến storage engine.
• Đọc phiên bản, cổng, engine mặc định và bộ ký tự mặc định của một server bằng đúng một câu SELECT.
• Giải thích vì sao bảng CREATE TABLE không khai báo gì vẫn là InnoDB và utf8mb4.
• Dự đoán kết quả ROLLBACK trên bảng InnoDB và bảng MyISAM, và biết khi nào utf8 không đủ chứa một ký tự.

Bức tranh lớn

Xuyên suốt chương 7, MySQL là một tòa nhà văn phòng. Cổng bảo vệ nhận khách (kết nối), tầng lễ tân hiểu khách cần gì và quyết định cách làm (tầng SQL), còn kho hồ sơ cất giữ thật sự (storage engine). Dự án xuyên suốt là ShopBook, với các bảng books và customers.

Hình dưới trả lời một câu hỏi: khi bạn gõ một lệnh vào mysql, những thành phần nào tham gia trước khi dữ liệu nằm xuống đĩa?

Cách đọc hình: đọc từ ô xanh dương trên cùng xuống dưới. Khung chữ mysqld là một chương trình duy nhất, bên trong có hai tầng tím. Tầng SQL đọc và lập kế hoạch cho câu lệnh, storage engine thực sự đọc ghi dữ liệu. Ô xanh ngọc ở dưới cùng là tệp trên đĩa. Màu chỉ là tín hiệu phụ, chữ trong ô mới là nội dung chính.

Chỗ ẩn dụ không còn đúng: lễ tân trong tòa nhà thật thường đứng một chỗ, còn tầng SQL của MySQL được dùng chung cho mọi kết nối cùng lúc, và mỗi kết nối có luồng xử lý riêng.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. Client và server là hai chương trình khác nhau

mysql là client, một chương trình nhỏ chỉ biết gửi câu lệnh và in kết quả. mysqld là server (daemon), chương trình giữ dữ liệu và chờ kết nối ở cổng mặc định 3306. Một máy có thể chỉ cài client, và một server có thể phục vụ nhiều client cùng lúc.

2. Tầng SQL và storage engine

Tầng SQL hiểu cú pháp, kiểm tra quyền và chọn cách thực hiện. Storage engine là phần cất và lấy dữ liệu thật. Mỗi bảng chọn một engine riêng, và tầng SQL nói chuyện với mọi engine bằng cùng một giao diện.

3. InnoDB là engine mặc định

InnoDB hỗ trợ giao dịch (ACID), khóa ở mức hàng và khóa ngoại. Từ MySQL 5.5.5 nó là engine mặc định, nên CREATE TABLE không khai báo gì sẽ ra bảng InnoDB. MyISAM là engine cũ, không có giao dịch và không có khóa ngoại.

4. Bộ ký tự: utf8 chưa phải UTF-8 đầy đủ

Trong MySQL, utf8 (tên mới là utf8mb3) chỉ lưu tối đa 3 byte cho mỗi ký tự. Bộ ký tự UTF-8 đầy đủ của MySQL là utf8mb4, tối đa 4 byte. Tiếng Việt có dấu nằm gọn trong 3 byte, nhưng những chữ Hán hiếm, emoji và nhiều ký hiệu khác cần 4 byte.

Ví dụ đầu tiên

Ví dụ nhỏ nhất: hỏi server của lab cho biết nó là ai.

SELECT VERSION() AS version, @@port AS port, @@default_storage_engine AS engine,
       @@character_set_server AS charset, @@collation_server AS collation;

Dự đoán trước khi xem kết quả: engine mặc định và bộ ký tự mặc định sẽ là gì?

Kết quả [Đã chạy: MySQL 8.0.44]:

+---------+------+--------+---------+--------------------+
| version | port | engine | charset | collation          |
+---------+------+--------+---------+--------------------+
| 8.0.44  | 3306 | InnoDB | utf8mb4 | utf8mb4_0900_ai_ci |
+---------+------+--------+---------+--------------------+

Bây giờ tạo bảng đầu tiên của ShopBook mà không khai báo gì về engine hay ký tự, rồi hỏi lại MySQL nó đã chọn gì:

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  price INT NOT NULL
);
SHOW CREATE TABLE books\G

*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `price` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Hai chương trình client và server cũng có thể kiểm tra riêng. Mật khẩu root của lab đã được đưa sẵn qua biến môi trường, nên các lệnh dưới đây không hỏi mật khẩu:

mysql --version
mysqld --version
mysqladmin -u root ping

mysql  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)
/usr/sbin/mysqld  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)
mysqld is alive

Xem từng bước

Animation dưới đây chạy một câu INSERT rồi ROLLBACK, và cho bạn so sánh hai engine. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: MySQL 8.0.44]. Bảng có một hàng chèn trong giao dịch rồi ROLLBACK:

CREATE TABLE log_inno (id INT) ENGINE=InnoDB;
CREATE TABLE log_myisam (id INT) ENGINE=MyISAM;
START TRANSACTION;
INSERT INTO log_inno VALUES (1);
INSERT INTO log_myisam VALUES (1);
ROLLBACK;
SELECT (SELECT COUNT(*) FROM log_inno) AS innodb_rows,
       (SELECT COUNT(*) FROM log_myisam) AS myisam_rows;

+-------------+-------------+
| innodb_rows | myisam_rows |
+-------------+-------------+
|           0 |           1 |
+-------------+-------------+

Bạn vừa làm được: nhìn một server MySQL và nói được phiên bản, engine, bộ ký tự của nó, và biết vì sao bảng InnoDB và bảng MyISAM phản ứng khác nhau với ROLLBACK.

Concept Check

Câu 1. Bạn gõ CREATE TABLE notes (id INT) không ghi ENGINE=. Bảng dùng engine nào trên server của lab?

• A. MyISAM, vì đó là engine nhanh nhất cho bảng nhỏ.
• B. InnoDB, vì đó là giá trị của default_storage_engine.
• C. MEMORY, vì bảng chưa có dữ liệu nên chỉ cần lưu trong RAM.
• D. Không có engine nào cho đến khi bạn chèn hàng đầu tiên.

Câu 2. Điều nào đúng về tầng SQL?

• A. Mỗi engine có một tầng SQL riêng với cú pháp khác nhau.
• B. Tầng SQL chỉ chạy khi bảng dùng InnoDB.
• C. Tầng SQL phân tích và lập kế hoạch cho mọi câu lệnh, rồi giao việc cho engine của bảng.
• D. Tầng SQL ghi trực tiếp dữ liệu xuống đĩa, engine chỉ lo giao dịch.

Câu 3. Cột title VARCHAR(100) với utf8mb3 nhận được chuỗi nào?

• A. Mọi chuỗi UTF-8, kể cả emoji và chữ Hán hiếm.
• B. Chỉ chữ không dấu, vì utf8mb3 không biết tiếng Việt.
• C. Chỉ các chuỗi dài đúng 100 byte.
• D. Tiếng Việt có dấu, nhưng không nhận ký tự cần 4 byte.

Đáp án và giải thích:

• Câu 1: B. Server của lab có default_storage_engine là InnoDB (xem kết quả ở ví dụ đầu). A sai vì MyISAM chỉ dùng khi bạn ghi rõ. C sai vì MEMORY cũng phải ghi rõ. D sai vì engine được gán ngay lúc tạo bảng.
• Câu 2: C. A sai vì cú pháp SQL chung cho mọi engine. B sai vì tầng SQL luôn chạy. D sai vì chính engine mới là nơi đọc ghi dữ liệu.
• Câu 3: D. A sai vì ký tự 4 byte bị từ chối. B sai vì tiếng Việt có dấu chỉ cần tối đa 3 byte. C sai vì 100 là số ký tự, không phải số byte.

Làm cùng mình

Bạn cần bảng customers cho ShopBook, rồi kiểm tra MySQL đã chọn gì thay bạn.

Bước 1. Vẽ ra giấy

Trước khi gõ, vẽ ba ô: tên bảng, engine bạn dự đoán, collation bạn dự đoán. Ghi thêm một dòng: tên 'Nguyễn Thị Hoa' dài bao nhiêu ký tự và bao nhiêu byte?

Bước 2. Tạo bảng và hỏi lại MySQL

Điền chỗ trống trong đoạn dưới:

-- TODO 1: tạo bảng customers có id tự tăng, full_name VARCHAR(100), email VARCHAR(120), không khai báo ENGINE hay CHARSET
-- TODO 2: lấy TABLE_NAME, ENGINE, TABLE_COLLATION của bảng customers từ information_schema.TABLES

Gợi ý: dùng WHERE TABLE_SCHEMA = DATABASE() để chỉ xem database hiện tại.

Bước 3. Chèn tên tiếng Việt

Chèn 'Nguyễn Thị Hoa' và so sánh CHAR_LENGTH(full_name) với LENGTH(full_name). Dự đoán trước: hai số này bằng nhau hay khác nhau, và vì sao? Đối chiếu với mục Lời giải và đáp án.

Bước 4. Vẽ lại ra giấy

Ghi một câu: vì sao tiếng Việt có dấu không cần utf8mb4 nhưng ShopBook vẫn nên dùng nó.

Thử phá nó đi

Ba lỗi người mới hay gặp [Đã chạy: MySQL 8.0.44]. Chữ trong cột thông báo lấy trực tiếp từ kết quả chạy.

SET NAMES utf8mb4;
CREATE TABLE name_mb3 (txt VARCHAR(20) CHARACTER SET utf8mb3);
CREATE TABLE name_mb4 (txt VARCHAR(20) CHARACTER SET utf8mb4);
INSERT INTO name_mb4 VALUES ('Yoshida 𠮷田');
SELECT txt, CHAR_LENGTH(txt) AS chars, LENGTH(txt) AS bytes FROM name_mb4;
INSERT INTO name_mb3 VALUES ('Yoshida 𠮷田');

+-----------------+-------+-------+
| txt             | chars | bytes |
+-----------------+-------+-------+
| Yoshida 𠮷田 |    10 |    15 |
+-----------------+-------+-------+
ERROR 1366 (HY000) at line 7: Incorrect string value: '\xF0\xA0\xAE\xB7\xE7\x94...' for column 'txt' at row 1

Kết quả chạy thật của dòng cuối cùng trong bảng, kèm câu CREATE TABLE mà MySQL ghi nhận. Dòng ROLLBACK ở giữa bảng đã có kết quả ở bảng trace phía trên:

CREATE TABLE t_utf8 (txt VARCHAR(10)) DEFAULT CHARSET=utf8;
SHOW WARNINGS;
SHOW CREATE TABLE t_utf8\G

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*************************** 1. row ***************************
       Table: t_utf8
Create Table: CREATE TABLE `t_utf8` (
  `txt` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

Hiểu lầm quan trọng nhất của bài là tin rằng mặc định luôn đúng với mọi nhu cầu. Hình dưới cho thấy mặc định của server làm giúp bạn việc gì.

Cách đọc hình: ô xanh dương là lệnh bạn gõ, ô cam là server tự áp dụng mặc định, ba nhánh xanh lá là hệ quả. Hai nhánh đầu (ENGINE, CHARSET) là hai quyết định bạn không viết ra. Hai ô cuối là điều bạn nhận được nhờ chúng.

Tự tay làm

Dùng chung database của lab và dự án ShopBook.

Cơ bản

Bài 1. Điền bảng trace. Đầu vào: hai bảng có đúng một cột id, một bảng InnoDB và một bảng MyISAM. Mỗi bảng chèn id = 1 trong một giao dịch rồi ROLLBACK. Điền bảng:

Tiêu chí đạt: điền đúng cả hai số và lý do. Gợi ý: nhớ ai chịu trách nhiệm hoàn tác.

Vận dụng

Bài 2. Tìm bảng không phải InnoDB. Đầu vào: tạo ba bảng t_inno (InnoDB), t_isam (MyISAM), t_mem (MEMORY), mỗi bảng một cột id INT. Viết một câu SELECT trên information_schema.TABLES liệt kê mọi bảng của database hiện tại có engine khác InnoDB, sắp xếp theo tên bảng. Tiêu chí đạt: chỉ hiện hai dòng t_isam và t_mem. Gợi ý: dùng ENGINE <> 'InnoDB'.

Thử thách (tùy chọn)

Bài 3. Cứu bảng MyISAM. Đầu vào: bảng log_myisam (MyISAM) đã có một hàng id = 1. Đổi sang InnoDB bằng một câu ALTER TABLE, rồi chứng minh ROLLBACK hoạt động: chèn id = 2 trong giao dịch, ROLLBACK, đếm hàng. Tiêu chí đạt: số hàng sau ROLLBACK vẫn là 1. Gợi ý: hàng cũ vẫn còn nguyên sau khi đổi engine.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài.

1. (Mục tiêu 1) Kể ra hai chương trình tham gia khi bạn gõ một câu lệnh, và việc của từng cái.
2. (Mục tiêu 2) Muốn biết cổng và engine mặc định của server, bạn viết một câu SELECT như thế nào?
3. (Mục tiêu 3) Vì sao CREATE TABLE không ghi ENGINE= mà bảng vẫn là InnoDB?
4. (Mục tiêu 4, dự đoán) Bảng MyISAM có 1 hàng, bạn chèn thêm 1 hàng trong giao dịch rồi ROLLBACK. Còn mấy hàng?
5. (Mục tiêu 4, tìm lỗi) Cột utf8mb3 từ chối chuỗi chứa 𠮷. Lỗi nằm ở đâu và sửa thế nào?

Điều cần nhớ

• MySQL gồm client (mysql) gửi lệnh và server (mysqld) giữ dữ liệu, mặc định ở cổng 3306.
• Tầng SQL xử lý mọi câu lệnh, rồi giao cho storage engine của bảng.
• InnoDB là engine mặc định, có giao dịch, khóa hàng và khóa ngoại. MyISAM thì không.
• utf8 thực chất là utf8mb3. Dùng utf8mb4 để lưu mọi ký tự Unicode.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: nếu server của bạn nằm trên máy khác, bạn cần biết những gì để mysql gõ cửa đúng chỗ?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Mỗi bộ ký tự có một collation mặc định. Trên server của lab:

SELECT CHARACTER_SET_NAME, DEFAULT_COLLATE_NAME FROM information_schema.CHARACTER_SETS WHERE CHARACTER_SET_NAME LIKE 'utf8%' ORDER BY 1;

+--------------------+----------------------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME |
+--------------------+----------------------+
| utf8mb3            | utf8mb3_general_ci   |
| utf8mb4            | utf8mb4_0900_ai_ci   |
+--------------------+----------------------+

[Suy luận] Collation utf8mb4_0900_ai_ci là mặc định của utf8mb4 từ MySQL 8.0, không phân biệt dấu và hoa thường (ai là accent-insensitive, ci là case-insensitive).

• So sánh với lựa chọn khác. MariaDB là bản fork của MySQL, cú pháp rất gần nhưng một số tính năng và mặc định đã tách khác nhau. Bài này chỉ nói về MySQL 8.0.
• Hiểu lầm nâng cao. Engine NDB (MySQL Cluster) và Archive cũng có trong danh sách của một số bản cài. Danh sách thực tế phụ thuộc vào bản cài, nên luôn kiểm tra bằng truy vấn như ở mục Concept Check.
• Giới hạn của bài. Bài này chưa nói về cách chọn collation, kích thước tệp dữ liệu hay cách InnoDB lưu trang. Danh sách engine đầy đủ của server lab:

SELECT ENGINE, SUPPORT, TRANSACTIONS, SAVEPOINTS
FROM information_schema.ENGINES
ORDER BY ENGINE;

+--------------------+---------+--------------+------------+
| ENGINE             | SUPPORT | TRANSACTIONS | SAVEPOINTS |
+--------------------+---------+--------------+------------+
| ARCHIVE            | YES     | NO           | NO         |
| BLACKHOLE          | YES     | NO           | NO         |
| CSV                | YES     | NO           | NO         |
| FEDERATED          | NO      | NULL         | NULL       |
| InnoDB             | DEFAULT | YES          | YES        |
| MEMORY             | YES     | NO           | NO         |
| MRG_MYISAM         | YES     | NO           | NO         |
| MyISAM             | YES     | NO           | NO         |
| ndbcluster         | NO      | NULL         | NULL       |
| ndbinfo            | NO      | NULL         | NULL       |
| PERFORMANCE_SCHEMA | YES     | NO           | NO         |
+--------------------+---------+--------------+------------+

• Đọc thêm. MySQL 8.0 Reference Manual, chương "The InnoDB Storage Engine" và "Character Sets, Collations, Unicode": https://dev.mysql.com/doc/refman/8.0/en/

Bài tiếp theo

Bài sau là kiểu dữ liệu ngày giờ: DATE, DATETIME và TIMESTAMP. Mini-dự án nối tiếp: thêm cột ngày đặt hàng cho ShopBook và xem điều gì xảy ra khi hai server nằm ở hai múi giờ khác nhau.

Lời giải và đáp án

Làm cùng mình, Bước 2. Hai việc cần làm:

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(120) NOT NULL
);
SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers';

+------------+--------+--------------------+
| TABLE_NAME | ENGINE | TABLE_COLLATION    |
+------------+--------+--------------------+
| customers  | InnoDB | utf8mb4_0900_ai_ci |
+------------+--------+--------------------+

Bảng tự thành InnoDB và collation utf8mb4_0900_ai_ci, vì bạn không khai báo gì và server áp dụng mặc định.

Làm cùng mình, Bước 3. Chèn tên tiếng Việt rồi đo:

SET NAMES utf8mb4;
INSERT INTO customers (full_name, email) VALUES ('Nguyễn Thị Hoa', 'hoa@example.com');
SELECT full_name, CHAR_LENGTH(full_name) AS chars, LENGTH(full_name) AS bytes FROM customers;

+--------------------+-------+-------+
| full_name          | chars | bytes |
+--------------------+-------+-------+
| Nguyễn Thị Hoa |    14 |    18 |
+--------------------+-------+-------+

Tên có 14 ký tự nhưng nhiều hơn 14 byte, vì mỗi chữ có dấu chiếm 2 hoặc 3 byte. utf8mb4 chứa được tất cả. Lý do ShopBook vẫn nên dùng utf8mb4: khách hàng có thể có tên chứa ký tự 4 byte, như ví dụ 𠮷田 ở đầu bài.

Bài 1. InnoDB: 0 hàng, vì InnoDB có nhật ký hoàn tác. MyISAM: 1 hàng, vì MyISAM ghi thẳng và không thể hoàn tác. Kết quả thật nằm ở bảng trace sau animation.

Bài 2.

CREATE TABLE t_inno (id INT) ENGINE=InnoDB;
CREATE TABLE t_isam (id INT) ENGINE=MyISAM;
CREATE TABLE t_mem (id INT) ENGINE=MEMORY;
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND ENGINE <> 'InnoDB'
ORDER BY TABLE_NAME;

+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| t_isam     | MyISAM |
| t_mem      | MEMORY |
+------------+--------+

Bài 3.

ALTER TABLE log_myisam ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO log_myisam VALUES (2);
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM log_myisam;

+---------------------+
| rows_after_rollback |
+---------------------+
|                   1 |
+---------------------+

Tự kiểm tra.

1. mysql (client) gửi câu lệnh và in kết quả. mysqld (server) giữ dữ liệu, nhận kết nối ở cổng 3306.
2. SELECT @@port, @@default_storage_engine;
3. Vì default_storage_engine của server là InnoDB, và MySQL dùng giá trị đó khi bạn không ghi ENGINE=.
4. 2 hàng, vì MyISAM không hoàn tác được hàng vừa chèn.
5. Lỗi ở chỗ cột dùng utf8mb3 (tối đa 3 byte mỗi ký tự) còn chữ 𠮷 cần 4 byte. Sửa bằng cách đổi cột sang utf8mb4.