SQL-检查列是否自动递增 [英] SQL - Check if a column auto increments

问题描述

我正在尝试运行查询以检查列是否自动递增.我可以检查类型，默认值，是否可以为空等，但是我不知道如何测试它是否自动递增.这是我测试其他内容的方式:

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'

很遗憾，没有与之比较的AUTO_INCREMENTS列.那么如何测试列是否自动递增?

Unfortunately there is no AUTO_INCREMENTS column to compare against. So how can I test if a column auto increments?

推荐答案

对于MySql，请检查EXTRA列:

For MySql, Check in the EXTRA column:

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%'

对于Sql Server，请使用sys.columns和is_identity列:

For Sql Server, use sys.columns and the is_identity column:

SELECT 
    is_identity
FROM sys.columns
WHERE 
    object_id = object_id('my_table')
    AND name = 'my_column'

这篇关于SQL-检查列是否自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！