一、MySQL默认值相关查询

1. 列出 MySQL 数据库中的表默认值select table_schema as database_name,

table_name,

column_name,

column_default

from information_schema.columns

where column_default is not null

and table_schema not in (information_schema, sys,

performance_schema,mysql)

-- and table_schema = your database name

order by table_schema,

table_name,

ordinal_position;

说明：

database_name - 数据库的名称（模式）table_name - 表的名称column_name - 列的名称column_default - 定义此列默认值的 SQL 表达式

2. MySQL数据库默认值汇总select column_default,

count(distinct concat(col.table_schema, ., col.table_name)) as tables,

count(column_name) as columns

from information_schema.columns col

join information_schema.tables tab on col.table_schema = tab.table_schema

and col.table_name = tab.table_name

where col.table_schema not in (sys, information_schema,

mysql, performance_schema)

and tab.table_type = BASE TABLE

group by column_default

order by tables desc,

columns desc;

说明：

column_default -为没有默认值的列定义默认约束（公式）和NULLtables- 具有此约束的表数（或具有对NULL行没有约束的列的表数）columns - 具有此特定约束的列数（或NULL行没有约束）

二、约束

1. 列出了数据库（模式）中的所有不可为空的列select tab.table_schema as database_name,

tab.table_name,

col.ordinal_position as column_id,

col.column_name,

col.data_type,

case when col.numeric_precision is not null

then col.numeric_precision

else col.character_maximum_length end as max_length,

case when col.datetime_precision is not null

then col.datetime_precision

when col.numeric_scale is not null

then col.numeric_scale

else 0 end as precision

from information_schema.tables as tab

join information_schema.columns as col

on col.table_schema = tab.table_schema

and col.table_name = tab.table_name

and col.is_nullable = no

where tab.table_schema not in (information_schema, sys,

mysql,performance_schema)

and tab.table_type = BASE TABLE

-- and tab.table_schema = database name

order by tab.table_schema,

tab.table_name,

col.ordinal_position;

注意：如果您需要特定数据库（模式）的信息，请取消注释 where 子句中的条件并提供您的数据库名称。

说明：

database_name - 数据库的名称（模式）table_name - 表的名称column_id - 列在表中的位置column_name - 列的名称data_type - 列数据类型max_length - 数据类型的最大长度precision- 数据类型的精度

2. 检查 MySQL 数据库中的列是否可为空select c.table_schema as database_name,

c.table_name,

c.column_name,

case c.is_nullable

when NO then not nullable

when YES then is nullable

end as nullable

from information_schema.columns c

join information_schema.tables t

on c.table_schema = t.table_schema

and c.table_name = t.table_name

where c.table_schema not in (mysql, sys, information_schema,

performance_schema)

and t.table_type = BASE TABLE

-- and t.table_schema = database_name -- put your database name here

order by t.table_schema,

t.table_name,

c.column_name;

说明：

database_name - 数据库名称（模式）table_name - 表名column_name - 列名nullable- 列的可空性属性：is nullable- 可以为空，not nullable- 不可为空