1.导入文件可能会导入\r或\n,
LOAD DATA LOCAL INFILE 'E:/gitlab/path/file1.txt' INTO TABLE `test`.`table1` FIELDS TERMINATED BY '\t'(Id,str)
需要加一下验证,UPDATE table1 SET str= REPLACE(REPLACE(str,'\r',''),'\n','')
2.嵌套中一定要写正确 列名字,否则,会一直执行不会结束:eg:
DELETE
FROM
`crawler_seeds_tagmap`
WHERE seedId IN
(SELECT
zgyid
FROM
zgyupdatetags20180806)
AND tagid IN
(SELECT
id
FROM
`crawler_seeds_tags`
WHERE DimentionInfo LIKE '%中改院%')
3.concat_ws尽量代替concat,这样分隔符不会产生多个.
4.进行join操作后要用group by查询,这样才会产生多个结果
5.while(locate(';',str)>0)这种操作保证str中包含';',否则不会反悔结果,,可以在循环前先确保str中包含';'
6.对于on后面加and还是where,如果加and,表示和on一起查询,如果加where表示先进行on查询,再用where筛选,如果只加and,那么作用的都是
UPDATE
new_crawler_seeds_table AS a
LEFT JOIN
b ON a.id = b.id
SET a.`str` = b.str WHERE 1=1 AND a.str1 LIKE '%str%'