MySQL使用时遇到的问题总结

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;