Shulou(Shulou.com)05/31 Report--

Editor to share with you how to view the table structure in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

First, briefly describe the table structure and field types

Desc tabl_name

Displays attributes such as table structure, field type, primary key, and whether it is empty, but does not display foreign keys.

For example: desc table_name

Second, query the comment information of the columns in the table

Select * from information_schema.columns

Where table_schema = 'db' # the database where the table resides

And table_name = 'tablename'; # the table you want to check

You can automatically select the information you need.

Third, only query column names and comments

Select column_name, column_comment from information_schema.columns where table_schema = 'db' and table_name =' tablename'

IV. # View the comments on the table

Select table_name,table_comment from information_schema.tables where table_schema = 'db' and table_name =' tablename'

5. View the DDL generated by the table

Show create table table_name

Although this command is not very easy to read, this is not a problem that can be ended with\ G, making the result easy to read; the command shows the DDL of creating the table, so the table structure, type, foreign keys, and comments are all displayed.

I prefer this command: the input is simple and the result is comprehensive.

Add some commands that may be used:

Table creation command:

CREATE TABLE `troomsoldorder` (

`id`int (11) NOT NULL AUTO_INCREMENT

`dt`date DEFAULT NULL COMMENT 'date'

`hour`tinyint (2) DEFAULT'0' COMMENT 'hour'

`hour_ order`int (11) DEFAULT'0' COMMENT 'orders per hour'

`total_ order`int (11) DEFAULT'0' COMMENT 'Total number of orders'

`promotion`int (11) DEFAULT'0' COMMENT 'Forecast orders'

PRIMARY KEY (`id`)

UNIQUE KEY `dt_ hour` (`dt`, `hour`)

) number of ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' real-time orders'

Table operation commands:

Replication table structure: create table table1 like table

Copy data: insert into table1 select * from table

Machine authorization:

Grant select on *. * to 'reader'@'%' identified by' 123456 'WITH GRANT OPTION

Flush privileges

Query data is inserted directly:

Insert into t_visual_user_domain (`user_ id`, `domain`, `group`) select id,'www.baidu.com' as domain, `group` from t_visual_user

Modify the table structure:

Alter table competitor_goods add sku_id bigint (20) unsigned DEFAULT NULL COMMENT 'merchandise sales code'.

The above is all the contents of the article "how to View the Table structure in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.