mysql 中的using

想在网上找关于mysql 里 using的用法，结果全是

 

SELECT * FROM table1   

JOIN table2 ON table1.id = table2.id

可以改成 

SELECT * FROM table1   

JOIN table2 USING(id)  

 

然后就没了

 

无奈只好自己查资料了

1.using在delete from 中的用法

 

delete 多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    tbl_name[.*] [, tbl_name[.*] ...]

    FROM table_references

    [WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    FROM tbl_name[.*] [, tbl_name[.*] ...]

    USING table_references

    [WHERE where_definition]

例子:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

或：

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

作用是只删除列于FROM子句之中（在USING子句之前）的表中的对应的行。作用是，您可以同时删除许多个表中的行，并使用其它的表进行搜索

再举个多表删除 删除重复记录的例子，这里要注意别名的用法

DELETE FROM t1 USING `t_player_soldier` AS `t1`,(

  SELECT DISTINCT MIN(`id`) AS `id`,`playerid`,`cfgid`

  FROM `t_player_soldier`

  GROUP BY `playerid`,`cfgid`

  HAVING COUNT(1) > 1

) AS `t2`

WHERE `t1`.`playerid` = `t2`.`playerid`

  AND `t1`.`cfgid` = `t2`.`cfgid`

  AND `t1`.`id` <> `t2`.`id`;

2.建立索引(这个大家都知道的)

CREATE TABLE lookup

  (id INT, INDEX USING BTREE (id))

  ENGINE = MEMORY;