`

SHOW COLUMNS Syntax

 
阅读更多
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

SHOW COLUMNS displays information about the columns in a given table. It also works for views as of MySQL 5.0.1. The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 18.19, “Extensions to SHOW Statements”.

mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

If the data types differ from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in Section 12.1.10.2, “Silent Column Specification Changes”.

The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. In other words, these two statements are equivalent:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW COLUMNS displays the following values for each table column:

Field indicates the column name.

Type indicates the column data type.

Collation indicates the collation for nonbinary string columns, or NULL for other columns. This value is displayed only if you use the FULL keyword.

The Null field contains YES if NULL values can be stored in the column. If not, the column contains NO as of MySQL 5.0.3, and '' before that.

The Key field indicates whether the column is indexed:

    If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

    If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

    If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL values.

    If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values.

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

Before MySQL 5.0.11, if the column permits NULL values, the Key value can be MUL even when a single-column UNIQUE index is used. The rationale was that multiple rows in a UNIQUE index can hold a NULL value if the column is not declared NOT NULL. As of MySQL 5.0.11, the display is UNI rather than MUL regardless of whether the column permits NULL; you can see from the Null field whether or not the column can contain NULL.

The Default field indicates the default value that is assigned to the column.

The Extra field contains any additional information that is available about a given column. The value is auto_increment for columns that have the AUTO_INCREMENT attribute and empty otherwise.

Privileges indicates the privileges you have for the column. This value is displayed only if you use the FULL keyword.

Comment indicates any comment the column has. This value is displayed only if you use the FULL keyword.

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See Section 12.8.1, “DESCRIBE Syntax”.

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables. See Section 12.4.5, “SHOW Syntax”.
分享到:
评论

相关推荐

    plsqldev13.0.0.1882x32主程序+ v12中文包+keygen

    You can manually enter the filter [removed]such as "owner, name" above, to show all columns with these 2 words in the name), or you can build a column filter by pressing the [...] button: A "Compare ...

    SQL Assistant v5.0

    "Show Keys and Indexed Columns" is preset for all assistance types by default. Primary keys, foreign keys, and indexed columns are displayed by default in column popups and mouse-over hints. Mouse-...

    SQLPrompt_7.3.0.651(包含注册机)

    Support ticket 76423: ambiguous columns are now always qualified with their table/alias inside an ORDER BY clause 7.3.0.642 - 27th October Support for user account licensing (More info) 7.3.0.639 - ...

    PLSQL.Developer v11.0.0.1762 主程序+ v10中文包+keygen

    The align button has a new Hide editor option to show only the result set The align button has a new Window width option to resize the SQL Window width to match the result set width You can now use an...

    plsqldev12.0.6.1832x32主程序+ v12中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    plsqldev12.0.6.1832x64主程序+ v12中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    Foundations for Analytics with Python O-Reilly-2016-Clinton W. Brownley

    The exam‐ ples in these sections show how to filter for specific rows and columns in the worksheets. After discussing how to read and parse any number of worksheets in a single workbook, the chapter...

    mysql ocp 实战整理题库,高命中率.docx

    A syntax error will result as you cannot specify more than one partition in the same statement. D. All data in p1 and p3 partitions are removed and the table definition is changed. DROPPARTITION can ...

    PLSQL.Developer(X32) v12.0.1.1814主程序+ v11中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    plsqldev12.0.4.1826x32主程序+ v12中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    PLSQL.Developer(X64) v12.0.1.1814 主程序+ v11中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    plsqldev12.0.4.1826x64主程序+ v12中文包+keygen

    Clicking on an item will show the text with syntax highlighting in the preview pane, and will show the timestamp above the preview pane. Double-click on an item to paste the text in the cursor ...

    WPTools.v6.29.1.Pro

    ******************************************* ************ WPTOOLS 6 History ************ ... Ziersch and **** **** WPCubed GmbH, Munich, Germany ******** ******************************************* ...

    TMS Pack for FireMonkey2.3.0.1

    New : Syntax highlighting and editing component TTMSFMXMemo New : Persisted Columns collection in TTMSFMXGrid New : KeyBoard and Mouse Wheel handling support in TTMSFMXSpinner Improved : Escape ...

    UE(官方下载)

    This tutorial will show you how to access the information you need in your browser by simply highlighting your text in the edit window and clicking your toolbar button How to install UE3 UE3 is the ...

    sqlmap (懂的入)

    exploiting and query syntax obviously depend upon the web application database management system back-end; * It recognizes valid queries by false ones based upon HTML output page hashes comparison ...

    FastReport.v4.15 for.Delphi.BCB.Full.Source企业版含ClientServer中文修正版支持D4-XE5

    - fixed bug in ODF export with properties table:number-columns-spanned, table:number-rows-spanned - fixed bug in ODF export with the background clNone color - fixed bug in ODF export with a style of ...

    JSP Simple Examples

    A two dimensional array can be thought as a grid of rows and columns. The first array will reflect to a row and the second one is column. int array Array is a collection of same data type. Suppose ...

    Delphi7.1 Update

    * When multiple indexes are included with the data from a provider, the IndexDef entries in the TClientDataset will show duplicate ‘Fields‘ and ‘DescFields‘ values (Quality Central 7543)....

Global site tag (gtag.js) - Google Analytics