mysql delete and or_Mysql delete操作

以下摘自官方文档：https://dev.mysql.com/doc/refman/5.7/en/insert.html

语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[PARTITION (partition_name,...)]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

性能:

When you do not need to know the number of deleted rows, the TRUNCATE TABLE statement is a faster way to empty a table than a DELETEstatement with no WHERE clause. Unlike DELETE, TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. SeeSection 14.1.34, “TRUNCATE TABLE Syntax” and Section 14.3.5, “LOCK TABLES and UNLOCK TABLES Syntax”.

简单理解是：truncate 在不锁表的情况下，很快：

如果想用delete删除快点：

The time required to delete individual rows in a MyISAM table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable

可以配置的key_buffer_size大小

多表删除：

(1)不带别名

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

(2)带别名：必须写别名：

If you declare an alias for a table, you must use the alias when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

Correct:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2

WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2

WHERE a1.id=a2.id;

2、删除mysql 中重复记录，并保留一条

DELETE FROM `tb_phones` WHERE id NOT IN(SELECT * FROM(SELECT id FROM `tb_phones` GROUP BY phone)AS b);