在mysql中,excel文件数据的导出和导入
数据的导出:
select * from mytable into outfile 'd:/mytable.xls';
你就可以在盘根目录下,发现mytable.xls文件
数据的导入:
用导出的表mytable.xls导入:输入命令:load data infile 'd:/mytable.xls' into table mytable;
注释:mytable :是数据库的表名
当数据库中的表有主键和自增字段时,会报错,数据无法添加,解决方案:
- 把mytable.xls表中的自增并且主键的那一列删除掉
- 输入命令:load data infile 'd:/mytable.xls' into table mytable(name,count);即可。数据是追加进去的,不是覆盖
- 自增字段 :auto_increment,主键:primary_key
-
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ...]