【MySQL】csv引擎
最近有个数据交换的接口，采用的是csv文件的方式。我们的数据库采用的是MySQL5.7，打算利用MySQL的csv引擎来解决数据导入的问题。
说明下，这个csv的引擎有点儿像Oracle的外部表，但是，不同的是Oracle外部表是只读的，而MySQL的csv引擎是可以对表进行DML操作。
做一个简单的测试：
1：建表（建表时要注意，csv引擎要求每个字段都不允许为空）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
## 我们看下面的报错提示很明显，字段不允许为空 mysql> create table tt(x int,y varchar(20)) engine=csv; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns mysql> ## 每个字段都加上非空的约束 mysql> create table tt(x int not null,y varchar(20) not null) engine=csv; Query OK, 0 rows affected (0.00 sec) mysql> mysql> desc tt; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | x | int(11) | NO | | NULL | | | y | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> |
2：我们去操作系统看一下csv引擎生针对刚刚创建的tt表成的文件
|
1 2 3 4 5 6 |
[root@msp xxfdb]# ls -l |grep tt -rw-r----- 1 mysql mysql 35 Mar 8 13:58 tt.CSM -rw-r----- 1 mysql mysql 0 Mar 8 13:54 tt.CSV -rw-r----- 1 mysql mysql 8578 Mar 8 13:54 tt.frm [root@msp xxfdb]# ## CSM:统计信息，CSV:数据，frm：表元数据 |
3：DML操作csv引擎表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
## 插入两条测试数据 mysql> insert into tt values(1,'zhangsan'); Query OK, 1 row affected (0.00 sec) mysql> insert into tt values(2,'zhuba'); Query OK, 1 row affected (0.00 sec) mysql> select * from tt; +---+----------+ | x | y | +---+----------+ | 1 | zhangsan | | 2 | zhuba | +---+----------+ 2 rows in set (0.00 sec) mysql> ## 执行更新操作 mysql> update tt set y='zhangsanfeng' where x=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | | 2 | zhuba | +---+--------------+ 2 rows in set (0.00 sec) mysql> ## 删除操作 mysql> delete from tt where x=2; Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | +---+--------------+ 1 row in set (0.00 sec) mysql> |
4：不允许创建索引
|
1 2 3 |
mysql> create index idx_t on tt(x); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed mysql> |
5：查看文件系统中csv的内容
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
## 我们可以看到，这个就是我们insert进来的数据，两条记录用逗号隔开，字符串用双引号 [root@msp xxfdb]# cat tt.CSV 1,"zhangsanfeng" [root@msp xxfdb]# ## 我们编辑一下这个文件，可以用vi编辑，也可以采用重定向的方式写入数据。我们采用重定向的方式试试 [root@msp xxfdb]# cat tt.CSV 1,"zhangsanfeng" 2,"lisi" [root@msp xxfdb]# ## 我们按照要求的格式写入了数据，返回数据库看看是否能查询的到。 mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | +---+--------------+ 1 row in set (0.00 sec) mysql> ## 显然直接查询是查询不到的，刷新一下表，将文件重新读取到内存，我们手工修改csv文件是完美的 mysql> flush table tt; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | | 2 | lisi | +---+--------------+ 2 rows in set (0.00 sec) mysql> |
6：字符型用单引号，或者不用引号，数据库是否识别？或者说识别是否有误
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
[root@msp xxfdb]# echo '3,'wangwu''>>tt.CSV [root@msp xxfdb]# echo '4,zhaoliu'>>tt.CSV [root@msp xxfdb]# [root@msp xxfdb]# cat tt.CSV 1,"zhangsanfeng" 2,"lisi" 3,wangwu 4,zhaoliu [root@msp xxfdb]# ## 回到mysql命令行 mysql> flush table tt; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | | 2 | lisi | | 3 | wangwu | | 4 | zhaoliu | +---+--------------+ 4 rows in set (0.00 sec) mysql> ## 我们可以看到，字符串在文本中，是否有双引号，在数据库层面都是支持的。 ## 如果在CSV中，字符串是单引号，会是什么效果呢？我们vi编辑一个单引号试试。 [root@msp xxfdb]# cat tt.CSV 1,"zhangsanfeng" 2,"lisi" 3,'wangwu' 4,zhaoliu 5,"zhuqi" [root@msp xxfdb]# ## 我们看wangwu已经改成单引号了，在数据库查询看看是什么情况？ mysql> flush table tt; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt; +---+--------------+ | x | y | +---+--------------+ | 1 | zhangsanfeng | | 2 | lisi | | 3 | 'wangwu' | | 4 | zhaoliu | | 5 | zhuqi | +---+--------------+ 5 rows in set (0.00 sec) mysql> ## 我们可以看到，显示的时候也会带上单引号。也就是说，字符串在csv中，要么是双引号，要么不需要引号 |