Tạo bảng từ CSV trong MySQL
Xem thửTình huống mở đầu
Nhà phát hành gửi cho Breeze Books file products.csv gồm 12.000 đầu sách, và bạn cần đưa chúng vào bảng products. Lần trước một đồng nghiệp viết vòng lặp INSERT từng dòng: chạy 9 phút rồi dừng ở dòng 3.417 vì giá ghi là abc, để lại 3.416 dòng nằm trong bảng và 8.583 dòng chưa vào. Không ai biết trong 8.583 dòng còn lại có thêm dòng bẩn nào nữa.
Trong bài lab, file thu gọn còn 5 dòng dữ liệu và 1 dòng bẩn. Hãy đoán trước: nếu nạp thẳng cả file vào bảng products có cột giá kiểu DECIMAL, sau lệnh đó bảng có mấy dòng? Cuối bài bạn sẽ đối chiếu với kết quả chạy thật.
Ôn nhanh bài trước: vì sao thiếu await trước conn.query(ddl) làm khối catch không bắt được lỗi?
Đáp án: lệnh được gửi đi nhưng try kết thúc ngay, lỗi đến sau thành unhandledRejection.
Mục tiêu bài học
Sau bài này bạn có thể:
- Tạo bảng trung gian (staging) và nạp file CSV bằng
LOAD DATA INFILEvới đúngFIELDS,LINESvàIGNORE. - Giải thích hai điều kiện để
LOAD DATAchạy được:secure_file_privcho file trên server, vàlocal_infileở cả hai phía choLOCAL. - Kiểm tra dữ liệu thô bằng
REGEXPrồi chuyển phần hợp lệ sang bảng thật bằngINSERT ... SELECT. - Nhận ra hai cái bẫy phổ biến: quên bỏ dòng tiêu đề và ký tự xuống dòng kiểu Windows.
Bức tranh lớn
Ẩn dụ của chương vẫn là phòng bookshop với các tủ hồ sơ. Lần này hàng từ nhà phát hành về dưới dạng một thùng giấy lộn xộn. Bạn không xếp thẳng vào tủ chính mà đổ ra một cái bàn nhận hàng (bảng trung gian), nhặt riêng những tờ rách, rồi chỉ chuyển những tờ sạch vào tủ. Dự án xuyên suốt là danh mục sách products của Breeze Books.
Hình dưới trả lời một câu hỏi: dữ liệu từ file CSV đi qua những bước nào trước khi vào bảng thật?
Đang tải sơ đồ…
Cách đọc hình: đọc từ ô xanh dương xuống. Hai ô tím là bước nạp thô vào bảng trung gian, nơi mọi cột là VARCHAR. Ô cam hỏi dòng có hợp lệ không: nhánh có đi xuống ô xanh lá là chuyển vào products, nhánh không đi sang ô đỏ là danh sách chờ sửa.
Chỗ ẩn dụ không còn đúng: cái bàn nhận hàng thật phải dọn đi sau khi xong, còn bảng trung gian có thể giữ lại để đối chiếu hoặc xóa bằng DROP TABLE khi không cần.
Khái niệm cốt lõi
Bài này có bốn khái niệm.
1. Bảng trung gian lỏng lẻo
Bảng trung gian (staging) có cột VARCHAR cho mọi thứ, không ràng buộc. Nạp vào đó không bao giờ bị lỗi chuyển kiểu, nên một dòng bẩn không làm dừng cả lệnh. Sau đó mới kiểm tra và chuyển sang bảng thật.
2. Lệnh nạp file
LOAD DATA INFILE 'đường_dẫn' INTO TABLE t đọc file nằm trên máy chủ MySQL. Các mệnh đề mô tả định dạng: FIELDS TERMINATED BY ',' (ký tự phân cột), ENCLOSED BY '"' (ký tự bao chuỗi), LINES TERMINATED BY '\n' (ký tự hết dòng), IGNORE 1 LINES (bỏ dòng tiêu đề) và danh sách cột đích. File phải nằm trong thư mục mà biến secure_file_priv cho phép.
3. LOCAL và local_infile
LOAD DATA LOCAL INFILE đọc file ở máy của client, tiện khi không có quyền vào máy chủ. Nó chỉ chạy khi local_infile bật ở cả server lẫn client, nếu không báo lỗi 3948.
4. Kiểm tra rồi chuyển
Dùng REGEXP hoặc điều kiện khác để tìm dòng bẩn, rồi INSERT ... SELECT ... WHERE chuyển phần sạch, kèm CAST sang kiểu thật. Dòng không đạt vẫn nằm trong bảng trung gian để sửa.
Ví dụ đầu tiên
File products.csv nằm ở thư mục cho phép của máy chủ, nội dung như sau (dòng đầu là tiêu đề, dòng thứ tư có giá là chữ):
sku,name,price
BK-001,Clean Code,35.50
BK-002,"Refactoring, 2nd Edition",42.00
BK-003,SQL Basics,12.00
BK-004,Bad Price Book,abc
BK-005,Domain Design,51.25
Trước tiên thử cách "ngây thơ": nạp thẳng vào bảng thật có giá kiểu DECIMAL.
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, name, price);
SELECT COUNT(*) AS rows_in_products FROM products;
Dự đoán trước khi xem: bảng products có mấy dòng sau lệnh này?
Kết quả [Đã chạy: MySQL 8.0.44]. Số dòng trong thông báo lỗi đã được bỏ đi, và thông báo nằm cuối vì bản chạy tự động gom lỗi riêng:
+------------------+
| rows_in_products |
+------------------+
| 0 |
+------------------+
ERROR 1366 (HY000): Incorrect decimal value: 'abc' for column 'price' at row 4
Một dòng bẩn làm dừng cả lệnh, và vì bảng InnoDB trả lại trạng thái trước lệnh nên cả 4 dòng sạch cũng không vào. Thông báo chỉ nói dòng thứ 4 (kể sau khi bỏ tiêu đề) sai, mà không liệt kê các dòng khác nếu còn.
Cách đúng là qua bảng trung gian:
CREATE TABLE staging_products (
sku VARCHAR(32),
name VARCHAR(120),
price VARCHAR(20) -- Giữ giá ở dạng chữ để dòng bẩn vẫn nạp được
);
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE staging_products
FIELDS TERMINATED BY ',' ENCLOSED BY '"' -- Dấu phẩy phân cột, dấu nháy kép bao chuỗi có dấu phẩy
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- Bỏ dòng tiêu đề
(sku, name, price);
SELECT sku, name, price FROM staging_products ORDER BY sku;
Kết quả [Đã chạy: MySQL 8.0.44]. Cả 5 dòng vào được, kể cả dòng giá abc, và tên có dấu phẩy được giữ nguyên nhờ dấu nháy kép:
+--------+--------------------------+-------+
| sku | name | price |
+--------+--------------------------+-------+
| BK-001 | Clean Code | 35.50 |
| BK-002 | Refactoring, 2nd Edition | 42.00 |
| BK-003 | SQL Basics | 12.00 |
| BK-004 | Bad Price Book | abc |
| BK-005 | Domain Design | 51.25 |
+--------+--------------------------+-------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
price VARCHAR(20) | Cột giá ở dạng chữ | Giá trị nào cũng nạp được, kể cả abc |
LOAD DATA INFILE '/var/lib/mysql-files/products.csv' | Đọc file trên máy chủ | Đường dẫn phải nằm trong thư mục secure_file_priv cho phép |
FIELDS TERMINATED BY ',' ENCLOSED BY '"' | Phân cột bằng dấu phẩy, chuỗi nằm trong dấu nháy kép | Nhờ nháy kép, tên Refactoring, 2nd Edition không bị tách thành hai cột |
LINES TERMINATED BY '\n' | Mỗi dòng kết thúc bằng ký tự xuống dòng | Khớp với file kiểu Unix, phần Thử phá nó đi sẽ chạy kiểu Windows |
IGNORE 1 LINES | Bỏ dòng đầu tiên | Dòng tiêu đề không phải dữ liệu |
(sku, name, price) | Ánh xạ cột thứ nhất, hai, ba của file vào cột đích | Rõ ràng về thứ tự, không phụ thuộc thứ tự cột của bảng |
Bước tiếp theo là kiểm tra và chuyển. Câu SELECT đầu liệt kê dòng bẩn, câu INSERT ... SELECT chuyển dòng sạch:
SELECT sku, price AS bad_price
FROM staging_products
WHERE NOT price REGEXP '^[0-9]+([.][0-9]+)?$';
INSERT INTO products (sku, name, price)
SELECT sku, name, CAST(price AS DECIMAL(10, 2))
FROM staging_products
WHERE price REGEXP '^[0-9]+([.][0-9]+)?$';
SELECT id, sku, price FROM products ORDER BY id;
Kết quả [Đã chạy: MySQL 8.0.44]. Một dòng bẩn được chỉ đích danh, và products có 4 dòng với giá đúng kiểu DECIMAL:
+--------+-----------+
| sku | bad_price |
+--------+-----------+
| BK-004 | abc |
+--------+-----------+
+----+--------+-------+
| id | sku | price |
+----+--------+-------+
| 1 | BK-001 | 35.50 |
| 2 | BK-002 | 42.00 |
| 3 | BK-003 | 12.00 |
| 4 | BK-005 | 51.25 |
+----+--------+-------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
WHERE NOT price REGEXP '^[0-9]+([.][0-9]+)?$' | Chọn dòng có giá không phải số, có thể có phần thập phân | Liệt kê trước khi chuyển, để biết dòng nào cần sửa |
INSERT INTO products (sku, name, price) SELECT ... | Chuyển dữ liệu từ bảng trung gian sang bảng thật | Một câu lệnh chuyển cả lô thay vì từng dòng |
CAST(price AS DECIMAL(10, 2)) | Đổi chữ thành số có 2 chữ số thập phân | Bảng thật yêu cầu kiểu DECIMAL |
WHERE price REGEXP '^[0-9]+([.][0-9]+)?$' | Chỉ lấy dòng có giá đúng dạng số | Dòng bẩn không lọt vào products |
Xem từng bước
Animation dưới đây chạy lại đường đi của 5 dòng từ file đến products. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.
Đang tải Animation…
Bảng trace bằng chữ [Đã chạy: MySQL 8.0.44]:
| Bước | staging_products | products | Dòng bị loại |
|---|---|---|---|
| Nạp thô | 5 dòng | 0 | 0 |
| Kiểm tra | 5 dòng | 0 | 1 (BK-004) |
| Chuyển dòng sạch | 5 dòng | 4 | 1 (BK-004) |
Bạn vừa làm được: nạp CSV mà không bị một dòng bẩn làm dừng cả lệnh, và nói chính xác dòng nào cần sửa.
Concept Check
Bảng staging_products gồm ba cột VARCHAR, file products.csv có tiêu đề và 5 dòng dữ liệu như trên.
Câu 1. LOAD DATA INFILE ... INTO TABLE staging_products ... (sku, name, price) thiếu IGNORE 1 LINES. Bảng có mấy dòng?
- A. 5 dòng, vì MySQL tự nhận ra dòng tiêu đề.
- B. 5 dòng, vì dòng tiêu đề bị lỗi nên không nạp.
- C. 4 dòng, vì dòng bẩn bị bỏ.
- D. 6 dòng, vì dòng tiêu đề cũng được nạp như một dòng dữ liệu.
Câu 2. Nạp thẳng file vào bảng products có price DECIMAL(10, 2) NOT NULL. Kết quả là gì?
- A. Cả 5 dòng vào, dòng
abcthành 0. - B. Lỗi 1366, và không dòng nào vào bảng vì cả lệnh bị hủy.
- C. 4 dòng sạch vào, dòng bẩn bị bỏ qua lặng lẽ.
- D. Lỗi 1290, vì file nằm ngoài thư mục cho phép.
Câu 3. Chạy LOAD DATA LOCAL INFILE mà server để local_infile bằng 0. Lỗi nào xuất hiện?
- A. Lỗi 1290, vì file nằm ngoài
secure_file_priv. - B. Lỗi 1366, vì dòng dữ liệu sai kiểu.
- C. Lỗi 1050, vì bảng đích đã tồn tại.
- D. Lỗi 3948, vì phải bật
local_infileở cả server và client.
Đáp án và giải thích:
- Câu 1: D. A sai vì MySQL không tự đoán tiêu đề. B sai vì bảng
VARCHARnhận mọi chữ. C sai vì dòngabcvẫn hợp lệ trong cột chữ. - Câu 2: B. A sai vì chế độ strict không đổi
abcthành 0 ngầm. C sai vì lệnh không bỏ qua dòng lỗi. D sai vì file nằm đúng thư mục cho phép. - Câu 3: D. A sai vì lỗi 1290 là của
INFILEthường. B và C không liên quan đến việc đọc file.
Làm cùng mình
Nhà phát hành gửi file mới, bạn cần nạp vào bảng trung gian rồi chuyển các sách có giá lớn hơn 0 vào products, đồng thời liệt kê những mã sách bị loại.
Bước 1. Vẽ ra giấy
Trước khi gõ, ghi ra giấy ba bước: nạp thô, kiểm tra, chuyển. Ghi dự đoán: với file của bài, bao nhiêu dòng được chuyển và dòng nào bị loại.
Bước 2. Điền chỗ trống
Giả sử staging_products đã nạp xong 5 dòng như Ví dụ đầu tiên:
-- TODO 1: INSERT vào products (sku, name, price) từ staging_products
-- TODO 2: chỉ lấy dòng có price đúng dạng số bằng REGEXP, và giá sau CAST lớn hơn 0
-- TODO 3: in ROW_COUNT() để biết đã chuyển bao nhiêu dòng
-- TODO 4: liệt kê sku và price của dòng chưa có mặt trong products
Gợi ý: chỗ trống thứ tư dùng NOT EXISTS (SELECT 1 FROM products p WHERE p.sku = s.sku). Nhớ SELECT ROW_COUNT() phải chạy ngay sau lệnh INSERT.
Chạy xong bạn sẽ thấy [Đã chạy] một con số chuyển và danh sách dòng bị loại. Đối chiếu với dự đoán ở Bước 1, đáp án ở mục Lời giải và đáp án.
Bước 3. Vẽ lại ra giấy
Ghi một câu: dòng bị loại đi đâu, và vì sao bạn không xóa nó khỏi bảng trung gian ngay.
Thử phá nó đi
Ba lỗi người mới hay gặp khi nạp CSV [Đã chạy: MySQL 8.0.44]:
| Code lỗi | Hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|---|
LOAD DATA không có IGNORE 1 LINES | Dòng tiêu đề thành một dòng dữ liệu | File có dòng tiêu đề | Thêm IGNORE 1 LINES |
File Windows nạp với LINES TERMINATED BY '\n' | Cột cuối mang thêm ký tự \r ẩn | Windows kết thúc dòng bằng hai ký tự \r\n | Dùng LINES TERMINATED BY '\r\n' |
| Đường dẫn ngoài thư mục cho phép | ERROR 1290 | Cấu hình secure_file_priv | Đặt file vào thư mục đó, hoặc dùng LOCAL |
Lỗi đầu tiên, quên bỏ tiêu đề:
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE staging_products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(sku, name, price);
SELECT sku, price FROM staging_products ORDER BY sku LIMIT 2;
SELECT COUNT(*) AS rows_loaded FROM staging_products;
Kết quả [Đã chạy: MySQL 8.0.44]. Dòng tiêu đề thành dòng đầu tiên của bảng, và bảng có 6 dòng thay vì 5:
+--------+-------+
| sku | price |
+--------+-------+
| BK-001 | 35.50 |
| BK-002 | 42.00 |
+--------+-------+
+-------------+
| rows_loaded |
+-------------+
| 6 |
+-------------+
Lỗi thứ hai là bẫy khó thấy nhất. File dùng kết thúc dòng kiểu Windows, nhưng lệnh khai báo kiểu Unix:
LOAD DATA INFILE '/var/lib/mysql-files/products_crlf.csv'
INTO TABLE staging_products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, name, price);
SELECT sku, REPLACE(price, CHAR(13), '[CR]') AS price_shown, LENGTH(price) AS len, HEX(RIGHT(price, 1)) AS last_byte FROM staging_products;
SELECT COUNT(*) AS found_by_equals FROM staging_products WHERE price = '35.50';
Kết quả [Đã chạy: MySQL 8.0.44]. Để thấy ký tự ẩn, câu SELECT thay nó bằng chữ [CR]. Cột price dài 6 byte thay vì 5, byte cuối là 0D (ký tự \r), và phép so sánh price = '35.50' không tìm thấy dòng nào:
+--------+-------------+------+-----------+
| sku | price_shown | len | last_byte |
+--------+-------------+------+-----------+
| BK-001 | 35.50[CR] | 6 | 0D |
| BK-002 | 12.00[CR] | 6 | 0D |
+--------+-------------+------+-----------+
+-----------------+
| found_by_equals |
+-----------------+
| 0 |
+-----------------+
Khai báo đúng '\r\n':
LOAD DATA INFILE '/var/lib/mysql-files/products_crlf.csv'
INTO TABLE staging_products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(sku, name, price);
SELECT sku, price, LENGTH(price) AS len FROM staging_products;
SELECT COUNT(*) AS found_by_equals FROM staging_products WHERE price = '35.50';
Kết quả [Đã chạy: MySQL 8.0.44]. Giá dài 5 byte và phép so sánh tìm được 1 dòng:
+--------+-------+------+
| sku | price | len |
+--------+-------+------+
| BK-001 | 35.50 | 5 |
| BK-002 | 12.00 | 5 |
+--------+-------+------+
+-----------------+
| found_by_equals |
+-----------------+
| 1 |
+-----------------+
Cẩn thận: một ký tự \r thừa không hiện ra khi in bảng, nhưng làm WHERE price = '35.50' trả về 0 dòng. Khi dữ liệu nạp xong mà phép so sánh bằng không khớp, hãy kiểm tra LENGTH và HEX của giá trị.
Lỗi thứ ba là đường dẫn và file không tồn tại:
LOAD DATA INFILE '/tmp/products.csv' INTO TABLE staging_products FIELDS TERMINATED BY ',' IGNORE 1 LINES;
LOAD DATA INFILE '/var/lib/mysql-files/nothere.csv' INTO TABLE staging_products FIELDS TERMINATED BY ',';
SELECT @@secure_file_priv AS allowed_folder;
Kết quả [Đã chạy: MySQL 8.0.44]. Đường dẫn ngoài thư mục cho phép bị từ chối bằng 1290. Đường dẫn trong thư mục nhưng file không có thì báo lỗi 13. Thư mục cho phép nằm ở cuối:
+-----------------------+
| allowed_folder |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/nothere.csv' (OS errno 2 - No such file or directory)
Hình dưới tóm tắt cách chẩn đoán khi LOAD DATA thất bại.
Đang tải sơ đồ…
Cách đọc hình: đọc từ ô xanh dương xuống ô cam đầu tiên: có dùng LOCAL không. Không LOCAL thì ô cam kế tiếp kiểm tra file có nằm trong secure_file_priv không (không thì lỗi 1290). Có LOCAL thì ô cam bên phải kiểm tra local_infile đã bật ở cả hai phía chưa (không thì lỗi 3948). Qua hết thì ô tím: kiểm tra tên file và định dạng.
Chạy thật LOCAL: lần đầu server chưa bật, lần sau bật tạm rồi tắt lại ngay. Lệnh mysql ở đây chạy trong cùng container với server, nên /tmp/products.csv là file của client:
mysql -t --local-infile=1 bookshop -e "LOAD DATA LOCAL INFILE '/tmp/products.csv' INTO TABLE staging_products FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (sku, name, price)"
mysql -u root -p -e "SET GLOBAL local_infile = 1"
mysql -t --local-infile=1 bookshop -e "LOAD DATA LOCAL INFILE '/tmp/products.csv' INTO TABLE staging_products FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (sku, name, price); SELECT COUNT(*) AS rows_loaded FROM staging_products"
mysql -u root -p -e "SET GLOBAL local_infile = 0"
Kết quả [Đã chạy: MySQL 8.0.44]. Lần đầu bị từ chối vì server tắt local_infile. Sau khi bật, 5 dòng được nạp:
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides
+-------------+
| rows_loaded |
+-------------+
| 5 |
+-------------+
Cẩn thận: local_infile mặc định tắt là có chủ đích. Một server bật nó cho phép client đọc file của mình lên server, rủi ro nếu kết nối tới server không tin cậy. Chỉ bật khi cần, và tắt lại sau khi nạp xong.
Tự tay làm
Dùng chung bảng staging_products và products của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.
Cơ bản
Bài 1. Đếm dòng tiêu đề. Đầu vào: file 5 dòng dữ liệu và một dòng tiêu đề. Dự đoán số dòng trong staging_products khi có và khi không có IGNORE 1 LINES. Tiêu chí đạt: 5 và 6. Gợi ý: nhìn kết quả ở phần Thử phá nó đi.
Vận dụng
Bài 2. Chuyển dòng sạch có điều kiện giá. Đầu vào: yêu cầu ở phần Làm cùng mình. Viết và chạy các lệnh. Tiêu chí đạt: 4 dòng được chuyển, và danh sách bị loại gồm đúng BK-004 với giá abc. Gợi ý: kết hợp REGEXP và điều kiện CAST(...) > 0 trong cùng WHERE.
Thử thách (tùy chọn)
Bài 3. File Windows. Đầu vào: file products_crlf.csv kiểu Windows, 2 dòng dữ liệu. Nạp qua bảng trung gian với định dạng đúng, chuyển sang products, và kiểm tra giá trong products. Tiêu chí đạt: products có 2 dòng với giá 35.50 và 12.00. Gợi ý: LINES TERMINATED BY '\r\n'.
Tự kiểm tra
Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.
- (Mục tiêu 1, nhớ lại) Vì sao bảng trung gian dùng
VARCHARcho cả cột giá? - (Mục tiêu 2, nhớ lại) Ba mệnh đề mô tả định dạng của
LOAD DATAbạn đã dùng là gì? - (Mục tiêu 2, dự đoán) File nằm ở
/tmp/products.csv, bạn chạyLOAD DATA INFILE(khôngLOCAL) khisecure_file_privlà/var/lib/mysql-files/. Lỗi nào? - (Mục tiêu 3, giải thích cho bạn chưa học) Vì sao không nạp thẳng CSV vào bảng thật, kể cả khi bảng thật có cùng cột?
- (Mục tiêu 4, tìm lỗi) Sau khi nạp file Windows,
WHERE price = '35.50'trả về 0 dòng dù nhìn bảng thấy35.50. Nguyên nhân và cách sửa?
Điều cần nhớ
- Nạp CSV qua bảng trung gian toàn
VARCHAR, kiểm tra, rồi chuyển phần sạch sang bảng thật. LOAD DATA INFILEcần định dạng đúng:FIELDS,ENCLOSED BY,LINES,IGNORE; và file nằm trongsecure_file_priv.LOCALcầnlocal_infilebật ở cả server lẫn client, nếu không là lỗi 3948.- File Windows kết thúc dòng bằng
\r\n: khai báo sai làm cột cuối mang ký tự\rẩn.
Cheat sheet:
| Muốn | Viết | Kết quả |
|---|---|---|
| Bỏ dòng tiêu đề | IGNORE 1 LINES | Dòng đầu không vào bảng |
| Chuỗi có dấu phẩy | ENCLOSED BY '"' | Giữ nguyên trong một cột |
| File Windows | LINES TERMINATED BY '\r\n' | Không còn ký tự \r thừa |
| Tìm dòng giá bẩn | WHERE NOT price REGEXP '^[0-9]+([.][0-9]+)?$' | Danh sách dòng cần sửa |
| Chuyển dòng sạch | INSERT INTO t SELECT ... CAST(...) WHERE ... | Chỉ dòng hợp lệ vào bảng thật |
| Xem thư mục cho phép | SELECT @@secure_file_priv | Đường dẫn được phép |
Hình tóm tắt của bài là hai sơ đồ ở mục Bức tranh lớn và Thử phá nó đi.
Câu hỏi để dành cho ngày mai: với script Node.js chạy một lần, bạn xóa bảng trung gian ra sao, và vì sao tên bảng ghép từ biến lại cần escape?
Đào sâu
Phần này dành cho bạn tò mò.
- Cơ chế bên trong.
LOAD DATAđọc file trực tiếp ở phía server (hoặc nhận file từ client khiLOCAL) và ghi hàng loạt, nên nhanh hơnINSERTtừng dòng [Có thể khác nhau: tùy cấu hình và kích thước file]. Bài không đo tốc độ. - So sánh với lựa chọn khác. Công cụ giao diện như MySQL Workbench có trình nhập CSV, và
mysqlimportlà công cụ dòng lệnh dùng cùng cơ chế. Bài này không chạy các công cụ đó [Suy luận]. - Hiểu lầm nâng cao. Cột nạp thiếu trong file gây cảnh báo (hoặc lỗi ở chế độ strict) chứ không im lặng. Khi khai báo ít cột hơn số cột trong file, MySQL cắt phần dữ liệu thừa và báo lỗi hoặc cảnh báo tùy chế độ SQL [Suy luận: bài này không chạy trường hợp đó].
- Giới hạn của bài. Bài này chưa nói đến mã hóa tệp (
CHARACTER SET), trườngNULLtrong CSV,SET col = ...khi nạp, vàLOAD DATAsong song. - Đọc thêm. MySQL 8.0 Reference Manual, trang
LOAD DATAStatement: https://dev.mysql.com/doc/refman/8.0/en/load-data.html và mục Security Considerations forLOAD DATA LOCAL.
Bài tiếp theo
Bài sau là xóa bảng bằng Node.js: tách tên bảng cần xóa khỏi dữ liệu người dùng, dọn bảng trung gian theo tiền tố và hiểu sự khác nhau giữa DROP TABLE thường với IF EXISTS. Mini-dự án nối tiếp: viết script dọn các bảng staging_ sau khi nhập xong.
Lời giải và đáp án
Câu hỏi mở đầu. Nạp thẳng vào bảng products có DECIMAL thì lệnh bị hủy với lỗi 1366 và bảng có 0 dòng, kể cả những dòng sạch. Vì vậy 3.416 dòng "vào dở" trong tình huống mở đầu là kết quả của vòng lặp INSERT từng dòng, không phải của LOAD DATA.
Làm cùng mình, Bước 2.
INSERT INTO products (sku, name, price)
SELECT sku, name, CAST(price AS DECIMAL(10, 2))
FROM staging_products
WHERE price REGEXP '^[0-9]+([.][0-9]+)?$'
AND CAST(price AS DECIMAL(10, 2)) > 0;
SELECT ROW_COUNT() AS moved;
SELECT sku, price AS rejected_price
FROM staging_products s
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.sku = s.sku);
+-------+
| moved |
+-------+
| 4 |
+-------+
+--------+----------------+
| sku | rejected_price |
+--------+----------------+
| BK-004 | abc |
+--------+----------------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
WHERE price REGEXP '^[0-9]+([.][0-9]+)?$' AND CAST(...) > 0 | Lọc dòng có giá là số và lớn hơn 0 | Điều kiện đầu loại chữ, điều kiện sau loại giá 0 |
SELECT ROW_COUNT() AS moved; | Trả số dòng của lệnh INSERT ngay trước | Phải gọi sát sau, sau lệnh khác thì giá trị đổi |
NOT EXISTS (SELECT 1 FROM products p WHERE p.sku = s.sku) | Chọn dòng của staging chưa có trong products | Đó chính là danh sách bị loại |
Dòng BK-004 bị loại và giữ lại trong bảng trung gian để người phụ trách sửa giá.
Bài 1. 5 dòng khi có IGNORE 1 LINES, 6 dòng khi không có (thêm dòng tiêu đề).
Bài 2. Lời giải là đoạn lệnh ở Bước 2, với 4 dòng được chuyển và BK-004 bị loại.
Bài 3.
LOAD DATA INFILE '/var/lib/mysql-files/products_crlf.csv'
INTO TABLE staging_products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(sku, name, price);
INSERT INTO products (sku, name, price)
SELECT sku, name, CAST(price AS DECIMAL(10, 2)) FROM staging_products;
SELECT sku, price FROM products ORDER BY id;
+--------+-------+
| sku | price |
+--------+-------+
| BK-001 | 35.50 |
| BK-002 | 12.00 |
+--------+-------+
Với '\r\n', giá chỉ còn 5 byte nên CAST hợp lệ và products có 2 dòng đúng.
Tự kiểm tra.
- Để nạp được mọi giá trị, kể cả dữ liệu bẩn như
abc, và kiểm tra sau bằng câu lệnh thay vì bị lệnh nạp dừng giữa chừng. FIELDS TERMINATED BY(cùngENCLOSED BY),LINES TERMINATED BYvàIGNORE n LINES.- Lỗi 1290, vì
/tmpnằm ngoài thư mụcsecure_file_priv. - Một dòng bẩn làm hủy cả lệnh và thông báo lỗi không liệt kê hết các dòng bẩn. Bảng trung gian nhận tất cả và cho phép liệt kê dòng cần sửa.
- Cột cuối mang thêm ký tự
\rvì file kết thúc dòng kiểu Windows. Sửa bằngLINES TERMINATED BY '\r\n', hoặc xóa\rthừa trước khi so sánh.