MySQL SQL - 检查列是否自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13772019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Check if a column auto increments
提问by Aust
I am trying to run a query to check if a column auto increments. I can check type, default value, if it's nullable or not, etc. but I can't figure out how to test if it auto increments. Here is how I am testing for those other things:
我正在尝试运行查询以检查列是否自动递增。我可以检查类型、默认值、是否可以为空等，但我不知道如何测试它是否自动递增。这是我测试其他事情的方式：
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND COLUMN_NAME = 'my_column'
AND DATA_TYPE = 'int'
AND COLUMN_DEFAULT IS NULL
AND IS_NULLABLE = 'NO'
--AND AUTO_INCREMENTS = 'YES'
Unfortunately there is no AUTO_INCREMENTScolumn to compare against. So how can I test if a column auto increments?
不幸的是，没有AUTO_INCREMENTS可以比较的列。那么如何测试列是否自动递增？
回答by Michael Fredrickson
For MySql, Check in the EXTRAcolumn:
对于MySql，在EXTRA列中签入：
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND COLUMN_NAME = 'my_column'
AND DATA_TYPE = 'int'
AND COLUMN_DEFAULT IS NULL
AND IS_NULLABLE = 'NO'
AND EXTRA like '%auto_increment%'
For Sql Server, use sys.columnsand the is_identitycolumn:
对于 Sql Server，使用sys.columns和is_identity列：
SELECT
is_identity
FROM sys.columns
WHERE
object_id = object_id('my_table')
AND name = 'my_column'
回答by Kermit
Assuming MySQL, the EXTRAcolumn will indicate whether it is AUTO_INCREMENT.
假设使用 MySQL，该EXTRA列将指示它是否为AUTO_INCREMENT.
| TABLE_CATALOG | TABLE_SCHEMA | ... | EXTRA | ... | ------------------------------------------------------------- | def | db_2_00314 | ... | auto_increment | ... |
And for MSSQL, see here.
对于 MSSQL，请参见此处。
回答by Dalkiel
this works for sql server:
这适用于 sql 服务器：
Select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
回答by Johny
Run: describe 'table_name'; In column EXTRA is what you looking for
运行：描述'table_name'；在列 EXTRA 中是您要查找的内容
