在MySQL中选择不包含特定列的表

发布于 2024-10-21 11:45:53 字数 283 浏览 4 评论 0原文

我正在尝试选择所有没有名为“unique”的列的表。我可以使用以下方法选择所有包含该列的表：

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database';

是否有一种简单的方法来修改 SELECT 语句，以便列出所有没有该列的表？这看起来很简单，但我无法弄清楚，并且在论坛上找不到答案。

谢谢

原文

I am trying to select all tables that do not have column named 'unique'. I can select all tables that have it using:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database';

Is there a simple way to modify the SELECT statement so it lists all tables that do not have that column? This seems like it would be simple, but I can't figure it out, and can't find an answer on the forum.

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

给我一枪 2024-10-28 11:45:53

select
t.*
from INFORMATION_SCHEMA.TABLES as t
    left join INFORMATION_SCHEMA.COLUMNS as c
    on c.TABLE_NAME = t.TABLE_NAME
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.COLUMN_NAME = 'unique'
where c.COLUMN_NAME is null
and t.TABLE_SCHEMA = 'database'

select
t.*
from INFORMATION_SCHEMA.TABLES as t
    left join INFORMATION_SCHEMA.COLUMNS as c
    on c.TABLE_NAME = t.TABLE_NAME
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.COLUMN_NAME = 'unique'
where c.COLUMN_NAME is null
and t.TABLE_SCHEMA = 'database'

回复收藏 0 原文

情深缘浅 2024-10-28 11:45:53

如果服务器中仅存在一个数据库，则上述查询有效。如果没有，其他数据库中的表也会列出。考虑这个从特定数据库中选取表的简化版本。总而言之，这是一个很大的亮点！

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'DATABASE_NAME' 
AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME 
                            FROM INFORMATION_SCHEMA.COLUMNS 
                           WHERE COLUMN_NAME = 'FIELD_NAME' 
                             AND TABLE_SCHEMA ='DATABASE_NAME');

The above queries work, if only one database exists in the server. If not, tables from other databases will also be listed. Consider this simplified version that picks tables from a particular DB. All in all, this is a great highlight!

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'DATABASE_NAME' 
AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME 
                            FROM INFORMATION_SCHEMA.COLUMNS 
                           WHERE COLUMN_NAME = 'FIELD_NAME' 
                             AND TABLE_SCHEMA ='DATABASE_NAME');

回复收藏 0 原文

琴流音 2024-10-28 11:45:53

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN (
SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database')

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN (
SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database')

回复收藏 0 原文

对你而言 2024-10-28 11:45:53

一个简单的反转：

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN

(SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database');

A simple inversion:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN

(SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database');

回复收藏 0 原文

~没有更多了~