怎么把csv导入mysql_如何将csv文件导入到MySQL

如何将csv文件导入到MySQL？

将csv文件导入到MySQL

首先登录MySQL:mysql -u root -p123456

创建数据表，保证字段和csv文件中的列一致mysql> show DATABASES;

mysql> use TEST;

mysql> show TABLES;

mysql> CREATE TABLE mytable(

-> id INT NOT NULL AUTO_INCREMENT,

-> Qtime DATETIME NOT NULL,

-> Tprice FLOAT NOT NULL,

-> PRIMARY KEY ( id )

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看安全路径，也就是要将上传文件放到安全路径下，才能成功上传mysql> show variables like '%secure%';

+--------------------------+-----------------------+

| Variable_name | Value |

+--------------------------+-----------------------+

| require_secure_transport | OFF |

| secure_auth | ON |

| secure_file_priv | /var/lib/mysql-files/ |

+--------------------------+-----------------------+

在终端中复制文件到安全路径sudo cp /home/hadoop/文档/文件.csv /var/lib/mysql-files

然后在到mysql下执行mysql> load data infile '/var/lib/mysql-files/文件.csv' into table mytable fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';

导出文件类似mysql> select * from `table` load data infile '/文档/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';

更多相关技术文章，请访问PHP中文网！

本文原创发布php中文网，转载请注明出处，感谢您的尊重！