Mysql的存儲引擎之：CSV存儲引擎

CSV存儲引擎是基于CSV格式檔案存儲資料的。

建立CSV存儲引擎的表

建立一個基于csv存儲引擎的表table_csv(注意字段要非空)

[sql] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> create table table_csv(id int notnull ,name varchar(10) not null) engine=csv;
2. Query OK, 0 rows affected (0.00 sec)
mysql> create table table_csv(id int notnull ,name varchar(10) not null) engine=csv;Query OK, 0 rows affected (0.00 sec)      

插入資料測試一下：

[sql] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> insert into table_csv values(1,'a');
2. Query OK, 1 row affected (0.00 sec)
3. 
4. mysql> insert into table_csvvalues(2,'b');
5. Query OK, 1 row affected (0.00 sec)
6. 
7. mysql> select * from table_csv;
8. +----+----+
9. | id | name |
10. +----+----+
11. |  1 | a    |
12. |  2 | b    |
13. +----+----+
14. 2 rows in set (0.00 sec)      

mysql> insert into table_csv values(1,'a');Query OK, 1 row affected (0.00 sec)mysql> insert into table_csvvalues(2,'b');Query OK, 1 row affected (0.00 sec)mysql> select * from table_csv;+----+----+| id | name |+----+----+| 1 | a || 2 | b |+----+----+2 rows in set (0.00 sec)      

檢視建立的table_csv的資料檔案(有三個檔案)：

注：.CSM是表狀态及表的資料量檔案； .CSV是存放資料檔案； .frm是表結構定義檔案

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. [root@localhost test]# ll
2. -rw-rw---- 1 mysql mysql   35 Sep  1 14:29  table_csv.CSM
3. -rw-rw---- 1 mysql mysql   18 Sep  1 14:38  table_csv.CSV
4. -rw-rw---- 1 mysql mysql 8582 Sep  1 14:28  table_csv.frm
[root@localhost test]# ll-rw-rw---- 1 mysql mysql 35 Sep 1 14:29 table_csv.CSM-rw-rw---- 1 mysql mysql 18 Sep 1 14:38 table_csv.CSV-rw-rw---- 1 mysql mysql 8582 Sep 1 14:28 table_csv.frm      

向CSV檔案插入資料，再查詢表資料

既然是CSV格式的檔案，那麼就打開看看吧：

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. [root@localhost test]# more table_csv.CSV
2. 1,"a"
3. 2,"b"
[root@localhost test]# more table_csv.CSV1,"a"2,"b"      

試着編輯一下CSV檔案，加一行3,"c" ：

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. [root@localhost test]# vim table_csv.CSV
2. 1,"a"
3. 2,"b"
4. 3,"c"
[root@localhost test]# vim table_csv.CSV1,"a"2,"b"3,"c"      

再次檢視table_csv.表，看看情況如何：

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> select * from table_csv;
2. +----+----+
3. | id | name |
4. +----+----+
5. |  1 | a    |
6. |  2 | b    |
7. +----+----+
8. 2 rows in set (0.00 sec
mysql> select * from table_csv;+----+----+| id | name |+----+----+| 1 | a || 2 | b |+----+----+2 rows in set (0.00 sec      

貌似沒有變化，檢查和修複一下table_csv表：

檢查：

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> check table table_csv;
2. +----------------+-------+----------+----------+
3. | Table          | Op    | Msg_type | Msg_text |
4. +----------------+-------+----------+----------+
5. | test.table_csv |check  | error    | Corrupt  |
6. +----------------+-------+----------+----------+
7. 1 row in set (0.00 sec)
mysql> check table table_csv;+----------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+----------------+-------+----------+----------+| test.table_csv |check | error | Corrupt |+----------------+-------+----------+----------+1 row in set (0.00 sec)      

修複：

注意：修複時，如果發現檔案中有損壞的記錄行，那之後的資料也要全部丢失了

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> repair table table_csv;
2. +----------------+--------+----------+----------+
3. | Table          | Op     | Msg_type | Msg_text |
4. +----------------+--------+----------+----------+
5. | test.table_csv | repair | status   | OK       |
6. +----------------+--------+----------+----------+
7. 1 row in set (0.00 sec
mysql> repair table table_csv;+----------------+--------+----------+----------+| Table | Op | Msg_type | Msg_text |+----------------+--------+----------+----------+| test.table_csv | repair | status | OK |+----------------+--------+----------+----------+1 row in set (0.00 sec      

[plain] ​​view plain​​​ ​​copy​​​ ​​print​​​​?​​
1. mysql> select * from table_csv;
2. +----+----+
3. | id | name |
4. +----+----+
5. |  1 | a    |
6. |  2 | b    |
7. |  3 | c    |
8. +----+----+
9. 3 rows in set (0.01 sec