MySql--表信息相关

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 834 阅读时长 ≈ 3 分钟

摘要

表信息相关

查看建表语句（包括之后对表的修改）

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

# 只查看字段信息
mysql> desc actor;

查看表信息

方式1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from information_schema.TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='tbl_test_info'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test_db
     TABLE_NAME: tbl_test_info
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 199
 AVG_ROW_LENGTH: 7986
    DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 49152
      DATA_FREE: 4194304
 AUTO_INCREMENT: 394
    CREATE_TIME: 2022-09-08 03:52:27
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: 测试表

方式2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use test_db;
mysql> show table status like 'tbl_test_info'\G
*************************** 1. row ***************************
           Name: tbl_test_info
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 199
 Avg_row_length: 7986
    Data_length: 1589248
Max_data_length: 0
   Index_length: 49152
      Data_free: 4194304
 Auto_increment: 394
    Create_time: 2022-09-08 03:52:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options:
        Comment: 测试表

查看表字段信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 方式1
mysql> show full columns from test_db.tbl_test_info;
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| Field       | Type         | Collation   | Null | Key | Default           | Extra                                         | Privileges                      | Comment                     |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| id          | int          | NULL        | NO   | PRI | NULL              | auto_increment                                | select,insert,update,references | 主键                        |
| source_id   | int          | NULL        | NO   | MUL | NULL              |                                               | select,insert,update,references | 来源id                      |
| source      | int          | NULL        | NO   |     | NULL              |                                               | select,insert,update,references | 来源，1：百度           |
| name        | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 分类                        |
| name_cn     | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 分类-中文                   |
| age         | varchar(100) | utf8mb4_bin | NO   |     | NULL              |                                               | select,insert,update,references | 适合的年龄段                |
| bookNum     | int          | NULL        | YES  |     | 0                 |                                               | select,insert,update,references | 该分类下小说的数量          |
| create_time | timestamp    | NULL        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             | select,insert,update,references | 创建时间                    |
| update_time | timestamp    | NULL        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间                    |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+

# 方式2
mysql> select COLUMN_NAME 列名, COLUMN_TYPE 数据类型,DATA_TYPE 字段类型,CHARACTER_MAXIMUM_LENGTH 长度,IS_NULLABLE 是否为空,COLUMN_DEFAULT 默认值,COLUMN_COMMENT 备注 ,column_key 约束 from information_schema.columns  where table_schema='test_db' and table_name='tbl_test_info';
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| 列名        | 数据类型     | 字段类型     | 长度   | 是否为空     | 默认值            | 备注                        | 约束   |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| id          | int          | int          |   NULL | NO           | NULL              | 主键                        | PRI    |
| source_id   | int          | int          |   NULL | NO           | NULL              | 来源id                      | MUL    |
| source      | int          | int          |   NULL | NO           | NULL              | 来源，1：百度           |        |
| name        | varchar(100) | varchar      |    100 | NO           | NULL              | 分类                        |        |
| name_cn     | varchar(100) | varchar      |    100 | NO           | NULL              | 分类-中文                   |        |
| age         | varchar(100) | varchar      |    100 | NO           | NULL              | 适合的年龄段                |        |
| bookNum     | int          | int          |   NULL | YES          | 0                 | 该分类下小说的数量          |        |
| create_time | timestamp    | timestamp    |   NULL | NO           | CURRENT_TIMESTAMP | 创建时间                    |        |
| update_time | timestamp    | timestamp    |   NULL | NO           | CURRENT_TIMESTAMP | 更新时间                    |        |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+

基于其它表创建新的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 只创建表结构，完整表结构
mysql> create table table_new like table_old;
# 将老表数据导入新表，要求新表和老表的表结构必须一模一样
mysql> insert into table_new select * from table_old;
# 将老表数据导入新表，自己关联新表和老表的字段
mysql> insert into table_new(字段1,字段2,…….) select 字段1,字段2,…….  from table_old;
# 清空表数据
mysql> truncate table table_new;
# 删除表
mysql> drop table table_new;

# 创建新表的同时将老表数据导入，这种建表方式不会创建索引，不推荐使用
mysql> create table table_new select * from table_old;
# 只会创建表结构，同样不会创建索引等信息
mysql> create table table_new select * from table_old where 1=2;