1、MySQL导入csv文件
LOAD DATA INFILE '/tmp/all_macvendor_devtype.csv'
INTO TABLE tbl_devtype_script_all_macvendor_devtype_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(strkey, strdevtype);
FIELDS TERMINATED BY ',' -- 以","为分隔符(csv默认“,”分隔)
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n' --区分行
ignore 1 lines -- 忽略首行
(strkey, strdevtype); --导入表中部分字段,字段数量要与csv文件中列数相等
2、update更新语句
报错:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tbl_devtype AS b
UPDATE tbl_devtype_script_all_macvendor_devtype_temp AS a SET idevtype = b.idevtype
from tbl_devtype AS b
WHERE
a.strdevtype = b.strdevtype;
报错:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tbl_devtype AS b
原因:
解决方法:
UPDATE tbl_devtype_script_all_macvendor_devtype_temp AS a SET idevtype = (select idevtype from tbl_devtype AS b
WHERE
a.strdevtype = b.strdevtype);
3、执行mysql语句报错:Error: 1093
错误You can't specify target table 'tbl_devtype' for update in FROM clause 简单解决
mysql>delete from tbl_devtype where id in (select id from tbl_devtype where id < 5);
ERROR1093 (HY000): You can't specify target table 'tbl_devtype' for update in FROM clause
改为下面就OK
deletefrom tbl_devtype where id in(select * from ((select id from tbl_devtype where id <5) tmp);
主句(select * from (从句 temp)
4、MySQL报错:ERROR 1137 (HY000): Can't reopen table
ERROR 1137 (HY000): Can't reopen table: 'c'
UPDATE tbl_devbaseinfo AS a SET idevtype = (select idevtype FROM tbl_devtype_script_devbaseinfo_unknow_temp AS c
WHERE a.idevtype = 0
AND c.idevtype > 1
AND a.uiddevrecordid = b.uiddevrecordid
-- 对于总设备数小于200或者未知设备数超过1/4时,不修改设备类型,即脚本不生效
AND (select * from (select count(*) from tbl_devbaseinfo) tmp) >= 200
AND (select * from (select count(*) from tbl_devbaseinfo) tmp) * 3/4 >= (select count(*) from tbl_devtype_script_devbaseinfo_unknow_temp))
;
ERROR 1137 (HY000): Can't reopen table: 'c'
在一个查询中能且仅能引用一次 temporary 表,上述的那个查询中将会不work。这个SQL 将会报ERROR 1137: Can't reopen table: 'b'错误。
解决办法
所以我们需要修改这个SQL,使其在一个query中只使用到一次tempId表即可;
ERROR 1137 (HY000): Can't reopen table: 'c'
5、ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
mysql> LOAD DATA INFILE '/tmp/leagsoft_devtype.csv'
-> INTO TABLE tbl_devtype_script_leagsoft_devtype_temp
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> lines terminated by '\r\n'
-> ignore 1 lines
-> (strkey, strdevtype);
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
这个错误的原因是数据文件中的列数跟 MySQL 数据表字段数目没有完全匹配,
解决方法:
LOAD DATA INFILE '/tmp/leagsoft_devtype.csv'
INTO TABLE tbl_devtype_script_leagsoft_devtype_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(strkey, strdevtype, iflag);
注:
最后添加 iflag
6、ERROR 1045 (28000): Access denied for user 'wwhlzzf'@'%' (using password: YES)
LOAD DATA INFILE '/tmp/all_macvendor_devtype.csv'
INTO TABLE tbl_devtype_script_all_macvendor_devtype_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(strkey, strdevtype);
ERROR 1045 (28000): Access denied for user 'wwhlzzf'@'%' (using password: YES)
原因:
MySQL使用LOAD DATA LOCAL INFILE从文件中导入数据比insert语句要快,就是导入数据之前,必须要有文件,也就是说从文件中导入
解决方法:
在INFILE前面加上local,解决问题
LOAD DATA local INFILE '/tmp/all_macvendor_devtype.csv'
INTO TABLE tbl_devtype_script_all_macvendor_devtype_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(strkey, strdevtype);
--LOAD DATA INFILE修改为LOAD DATA local INFILE, 解决问题
下面是网上查到的load data local infile与load data infile的区别与注意事项
参考url: http://blog.sina.com.cn/s/blog_6fb90ed30100qrkr.html
如果你没有给出local,则服务器按如下方法对其进行定位:
1)如果你的filename为绝对路径,则服务器从根目录开始查找该文件.
2)如果你的filename为相对路径,则服务器从数据库的数据目录中开始查找该文件.
如果你给出了local,则文件将按以下方式进行定位:
1)如果你的filename为绝对路径,则客户机从根目录开始查找该文件.
2)如果你的filename为相对路径,则客户机从当前目录开始查找该文件.
(现在还不太理解,先暂做记录,如果有知道的,可以回复一下,谢谢)
Here are example uses of the LOAD DATA command:
-- source file is located in the MySQL Server data directory, is tab delimited, and the columns exactly match the table
LOAD DATA INFILE 'user_data.tsv' INTO TABLE users;
-- source file is local, is comma delimited and contains only some columns. Database is remote
LOAD DATA LOCAL INFILE '/tmp/user_data.csv'
INTO TABLE users (first_name, last_name, email)
FIELDS TERMINATED BY ',';
-- source file is comma delimited, strings are enclosed by double quotes, lines are terminated by carriage return/newline pairs, has a single header row that has to be ignored
LOAD DATA INFILE 'data.txt' INTO TABLE my_table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
-- Ignore a column in the source file by assigning it to a user variable and not assigning the variable to a table column
LOAD DATA INFILE 'data.txt'
INTO TABLE my_table_name (column1, @dummy, column2, @dummy, column3);
-- Populate a column the source file does not have any data for using the SET clause
LOAD DATA INFILE 'file_no_timestamps.txt'
INTO TABLE my_table_with_timestamps
(column1, column2)
SET timestamp_column = CURRENT_TIMESTAMP;