MySQL 如何获取列是否为主键

要获取某一列是否为主键，可以使用COLUMN_NAME和COLUMN_KEY=’PRI’。语法如下 –

select column_name, case when column_key= 'PRI' then 'yourMessage1' else ''yourMessage2' end as anyAliasName
from information_schema.columns
where table_schema =database()
and `table_name` = yourTableName
order by `table_name`, ordinal_position;

要理解以上语法，让我们创建一张表 –

mysql> create table DemoTable1886
  (
  Id int NOT NULL,
  FirstName varchar(20),
  LastName varchar(20),
  Age int,
  DateOfBirth datetime,
  Education varchar(40),
  PRIMARY KEY(Id)
  );
Query OK, 0 rows affected (0.00 sec)

这是获取特定列是否为主键的查询语句 –

mysql> select column_name, case when column_key= 'PRI' then 'This is a Primary key Column' else 'This is not a Primary key Column' end as Output
  from information_schema.columns
  where table_schema =database()
  and `table_name` = 'DemoTable1886'
  order by `table_name`, ordinal_position;

这将产生以下输出 –

+-------------+--------------------------------+
| COLUMN_NAME | Output                         |
+-------------+--------------------------------+
| Id          | This is a Primary key Column   |
| FirstName   |This is not a Primary key Column|
| LastName    |This is not a Primary key Column|
| Age         |This is not a Primary key Column|
| DateOfBirth |This is not a Primary key Column|
| Education   |This is not a Primary key Column|
+-------------+--------------------------------+
6 rows in set (0.00 sec)

阅读更多：MySQL 教程