本文共 2015 字，大约阅读时间需要 6 分钟。

环境

MacBook Pro

备份表并重命名

DROP TABLE IF EXISTS new_table;CREATE TABLE new_table LIKE old_table;INSERT INTO new_table SELECT * FROM old_table;-- 假设第三条SQL检测不通过，就把具体的字段写出来

=-=-=-=-=-=-=-=-=-=2020年8月10日-=-=-=-=-=-=-=start-=-=-=

create table tableName1 as select * from tableName2-- 如果不想同步数据create table tableName1 as select * from tableName2 where 1 = 2

=-=-=-=-=-=-=-=-=-=2020年8月10日-=-=-=-=-=-=-=end-=-=-=

update select

更新时使用临时表，但是该临时表的数据又来自于本表，

=-=-=-=-=-=-=-=-=-=2020年10月15日-=-=-=-=-=-=-=start-=-=-=

刚开始我是如下写法：

UPDATE xrshop.product AS aaaINNER JOIN  (	SELECT		we.sn,		we.productId	FROM		warehouse.examination we	WHERE		we.productId IN (			SELECT				p.id			FROM				xrshop.product AS p			WHERE				CODE IS NULL			AND p.majorCategoryId = 17			... 省略一些条件		)) AS b SET aaa.`code` =  b.snWHERE b.productId = aaa.id

这样总是报错：后来我改为：

UPDATE xrshop.product AS aaaINNER JOIN (select sn, productId from (	SELECT		we.sn,		we.productId	FROM		warehouse.examination we	WHERE		we.productId IN (			SELECT				p.id			FROM				xrshop.product AS p			WHERE				CODE IS NULL			AND p.majorCategoryId = 17			AND p.minorCategoryId IN (				17005,				17009,				17012,				17013,				17010,				17011,				17015			)		)) AS b ) bbbSET aaa.`code` = bbb.snWHERE bbb.productId = aaa.id

也就是对INNER JOIN后面的查询结果集，再用select包一下，然后就可以了；

delete select

如果删除表A的数据，是先从表A中查询出来的，则同理一样，

DELETE from xrshop.product WHERE id in (select a.id from (SELECT				p.id			FROM				xrshop.product AS p			WHERE				CODE IS NULL			AND p.majorCategoryId = 17			AND p.minorCategoryId IN (				17005,				17009,				17012,				17013,				17010,				17011,				17015			)) as a)

not in 优化

因为 not in不走索引，所以不在不得已情况下，就不要使用not in

下面使用 join 来替代not in 做查询

select ID from A where ID not in (select ID from B)

替换为

select A.ID from A left join B on A.ID=B.ID and B.ID is null

或者：

select A.ID from A left join B on A.ID=B.ID where B.ID is null