mysql - 为什么我不能在子选择中使用 INFORMATION_SCHEMA？

Question

我正在尝试使用 INFORMATION_SCHEMA 修剪数据库中的表。为了测试 DELETE，我使用 SELECT 来查看数据是否正确返回。我想根据数据库是否存在从 master.company 中删除数据。这是我尝试过的方法，但不起作用:

USE DATABASE master;

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `db_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

SELECT db_name FROM master.company WHERE db_name IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA);

但是，当我像这样反向引用表格时，我可以让它工作:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME in ( SELECT db_name FROM master.company);     

有什么想法吗？

Answer 1

参见10.1.7.9 Collation and INFORMATION_SCHEMA Searches .

尝试:

SELECT `c`.`db_name`
FROM `company` `c`
  INNER JOIN `information_schema`.`SCHEMATA` `iss`
  ON `c`.`db_name` = `iss`.`SCHEMA_NAME`;

更新1

它确实看起来更像是 IN 子句的错误。

参见 SQL Fiddle demo

尝试以下操作13.5 SQL Syntax for Prepared Statements :

SET @`qry` := CONCAT('
SELECT
  `id`,
  `domain`,
  `name`,
  `db_name`
FROM `company`
WHERE `db_name` IN (',
       (SELECT GROUP_CONCAT(CONCAT('\'', `SCHEMA_NAME`, '\''))
       FROM `information_schema`.`SCHEMATA`),
')');
PREPARE `stmt` FROM @`qry`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

SQL Fiddle demo

记住正确设置变量:group_concat_max_len .

更新2

在 MySQL 5.6 的早期版本中不会出现此问题:

SQL Fiddle demo MySQL 5.6 SQL Fiddle demo MySQL 5.5

报告的错误:Bug #77191 Subquery on information_schema including an IN clause, does not return results

更新3

解决方法:

SET @@session.optimizer_switch := 'semijoin=off';

SQL Fiddle demo MySQL 5.6