MySQL的存储引擎之CSV

CSV引擎以逗号分隔方式将数据存储在文本文件中。
创建CSV表时，MySQL在数据库目录中

• 创建表格式文件，文件名以表名开头，扩展名为.frm。
• 创建纯文本数据文件，文件名以表名开头，扩展名为.CSV。

将数据存储到表中时，CSV引擎以逗号分隔方式保存数据到文件中。

root@database-one 23:27:  [gftest]> create table testcsv(i INT NOT NULL, c CHAR(10) NOT NULL) engine=csv;
Query OK, 0 rows affected (0.12 sec)

root@database-one 23:29:  [gftest]> INSERT INTO testcsv VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@database-one 23:29:  [gftest]> select * from testcsv;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)

root@database-one 23:30:  [gftest]> exit
Bye
[root@database-one ~]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -l testcsv*
-rw-r----- 1 mysql mysql   35 3月   1 23:29 testcsv.CSM
-rw-r----- 1 mysql mysql   30 3月   1 23:29 testcsv.CSV
-rw-r----- 1 mysql mysql 8578 3月   1 23:28 testcsv.frm
[root@database-one gftest]# more testcsv.CSV
1,"record one"
2,"record two"

同学们可能注意到，除了.frm和.CSV外，还有个.CSM文件，这是什么？官方文档有说明：

Creating a CSV table also creates a corresponding metafile that stores the state of the table and the number of rows that exist in the table. The name of this file is the same as the name of the table with the extension CSM.

原来.CSM是元文件，保存表的状态和行数。

CSV引擎支持CHECK TABLE来检查表，REPAIR TABLE语句尽可能修复损坏的表。

root@database-one 23:42:  [gftest]> check table testcsv;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| gftest.testcsv | check | status   | OK       |
+----------------+-------+----------+----------+
1 row in set (0.02 sec)

上面的检查表正常，我们破坏下testcsv.CSV，将第二行数据两个列之间的,号删掉，再进行检查和修复。

[root@database-one gftest]# ls -l testcsv.*
-rw-r----- 1 mysql mysql   35 3月   1 23:48 testcsv.CSM
-rw-r----- 1 mysql mysql   30 3月   1 23:48 testcsv.CSV
-rw-r----- 1 mysql mysql 8578 3月   1 23:28 testcsv.frm
[root@database-one gftest]# vi testcsv.CSV
1,"record one"
2"record two"
~
"testcsv.CSV" 2L, 29C written
[root@database-one gftest]# more testcsv.CSV
1,"record one"
2"record two"
[root@database-one gftest]# mysql -uroot -h127.0.0.1 -P3306 -p********
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31661469
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@database-one 23:50:  [(none)]> use gftest;
Database changed
root@database-one 23:50:  [gftest]> check table testcsv;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| gftest.testcsv | check | error    | Corrupt  |
+----------------+-------+----------+----------+
1 row in set (0.01 sec)

root@database-one 23:50:  [gftest]> select * from testcsv;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
+---+------------+
1 row in set, 1 warning (0.01 sec)

上面看到，检查失败，表标记为已损坏（corrupt）。一旦表被标记为已损坏，下次运行CHECK TABLE或执行SELECT语句时，会自动修复。
手动修复和自动修复的效果一样，都是从现有CSV数据中复制尽可能多的有效行，然后用复制的行替换现有的CSV文件，任何损坏的数据都将丢失。

root@database-one 00:02:  [gftest]> truncate table testcsv;
Query OK, 0 rows affected (0.01 sec)

root@database-one 00:02:  [gftest]> INSERT INTO testcsv VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@database-one 00:02:  [gftest]> exit
Bye
[root@database-one gftest]# cd /home/mysql/gftest/
[root@database-one gftest]# vi testcsv.CSV
1,"record one"
2"record two"
~
"testcsv.CSV" 2L, 29C written
[root@database-one gftest]# more testcsv.CSV
1,"record one"
2"record two"
[root@database-one gftest]# mysql -uroot -h127.0.0.1 -P3306 -p********
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31662092
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@database-one 00:03:  [(none)]> use gftest;
Database changed
root@database-one 00:03:  [gftest]> check table testcsv;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| gftest.testcsv | check | error    | Corrupt  |
+----------------+-------+----------+----------+
1 row in set (0.03 sec)

root@database-one 00:03:  [gftest]> repair table testcsv;
+----------------+--------+----------+----------+
| Table          | Op     | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| gftest.testcsv | repair | status   | OK       |
+----------------+--------+----------+----------+
1 row in set (0.00 sec)

root@database-one 00:04:  [gftest]> select * from testcsv;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
+---+------------+
1 row in set (0.00 sec)

CSV引擎也有几个限制：

• 不支持索引
• 不支持分区
• 所有列都必须具有NOT NULL属性