如何在mysql中选择没有PRIMARY键的所有列? [英] how to select all columns without PRIMARY key in mysql?

问题描述

我将列出 mysql 中除 PRIMARY 键之外的表的所有列.注意:操作是自动的，所以我不知道字段的名称.你能帮我做这个查询吗?

解决方案

如果您打算使用任何长度的数据库，这是值得做一些研究的东西.

迄今为止我使用过的所有 DBMS 都有查看约束、列和表信息的方法.INFORMATION_SCHEMA 中的 MySQL 可能会帮助您做您想做的事情:

TABLE_CONSTRAINTS MySQL对此的参考是此处.>

SELECT table_name、constraint_name、constraint_type FROM INFORMATION_SCHEMA.table_constraints;

COLUMNS 对此的 MySQL 参考是 此处.

SELECT column_name FROM INFORMATION_SCHEMA.columns;

KEY_COLUMN_USAGE

你应该能够做这样的事情来得到你想要的:

SELECT INFORMATION_SCHEMA.key_column_usage.column_name来自 INFORMATION_SCHEMA.key_column_usage加入 INFORMATION_SCHEMA.table_constraintsON INFORMATION_SCHEMA.key_column_usage.column_name = INFORMATION_SCHEMA.table_constraints.column_nameWHERE INFORMATION_SCHEMA.table_constraints.constraint_type <>'首要的关键'

本质上应该是您所需要的.当需要获取有关您的架构的信息时，此类视图/表可能是您最好的朋友.

希望这些信息对您有所帮助.

I going to list all columns of a table except PRIMARY key in mysql. Note : operation is automatically so I don't know the names of fields. Can you help me for making this query?

解决方案

This is something worth doing some research on, if you are going to be working with databases in any length.

All DBMS I have worked with thus far have a means of looking at the constraints, columns, and table information. The ones for MySQL that will help you do what you want are likely in the INFORMATION_SCHEMA:

TABLE_CONSTRAINTS The MySQL Reference for this is here.

SELECT table_name, constraint_name, constraint_type FROM INFORMATION_SCHEMA.table_constraints;

COLUMNS The MySQL reference for this is here.

SELECT column_name FROM INFORMATION_SCHEMA.columns;

KEY_COLUMN_USAGE

You should be able to do something like this to get what you want:

SELECT INFORMATION_SCHEMA.key_column_usage.column_name 
FROM INFORMATION_SCHEMA.key_column_usage
JOIN INFORMATION_SCHEMA.table_constraints 
ON INFORMATION_SCHEMA.key_column_usage.column_name = INFORMATION_SCHEMA.table_constraints.column_name
WHERE INFORMATION_SCHEMA.table_constraints.constraint_type <> 'PRIMARY KEY'

The should be essentially what you need. Views/tables like these can be your best friend when needing to get information about your schema.

I hope that this information helps.

这篇关于如何在mysql中选择没有PRIMARY键的所有列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！