What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)
mysql> select count(table_name) from tables;
+-------------------+
| count(table_name) |
+-------------------+
| 2704 |
+-------------------+
1 row in set (5.33 sec)
mysql> select count(distinct table_name) from tables;
+----------------------------+
| count(distinct table_name) |
+----------------------------+
| 1427 |
+----------------------------+
1 row in set (3.16 sec)
first query:
select table_name as a from tables left join (select distinct table_name as b from tables) on a != b;
seems this query is quite inefficient, someone said left join could be better,
select table_name from tables where table_name not in (select distinct table_name from tables);
what is command means? first select a set (we name it set A)of 2704 items, then select the other set(we name it set B) which have 1427 items.
then run 2704 times to assure if the item in set A is in set B, if not, then that item will be added to the result set.
so how many comparation will be operated by mysql engine?is that 2704 x 1427?
or mysql engine may have some smart way to deal with this?
method 2 not works either:
mysql> select count(table_name) as num, table_name from tables group by table_name where num > 1;
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 'where num > 1' at line 1
mysql> select count(table_name) as num, table_name from tables where num>1 group by table_name ;
ERROR 1054 (42S22): Unknown column 'num' in 'where clause'
mysql> select count(table_name) as num, table_name from tables group by table_name where num > 1;
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 'where num > 1' at line 1
select count(table_name) as num, table_name from tables group by table_name having num > 1;
this query works well, why the former queries failed?
why this left join failed?
mysql> select table_name from tables as a left join (select distinct table_name from tables as b) on a.table_name!=b.table_name;
ERROR 1248 (42000): Every derived table must have its own alias
Best Answer
Your last query
fails because the
as b
must be outside the parentheses like thisYour method 2 failed
because the
group by table_name
must be last like thisSUGGESTION
Perhaps you should start with a
GROUP BY ... HAVING
query like thisThis will definitely give all tables whose name appears in multiple databases
Form that as a subquery and join it to gather all databases the table appears in
Please notice that I use
INNER JOIN
rather thanLEFT JOIN
because it will really form a Cartesian product (2704 X 2704) and then perform comparisons.I know this works because I tried it out in MySQL 5.5.12 on my Windows7 machine
Give it a Try !!!