表的介绍

  • 表是关系数据库的核心
  • 表 = 关系
  • 表是记录的集合
  • 二维表格模型易于人的理解
  • MySQL 默认存储引擎都是基于行存储
  • 每行记录都是基于列进行组织的
  1. select * from table_name limit 1;

表是数据的集合，集合是无序的，上面的 SQL 语句的意思是：从表（集合）中随机选出一条数据，结果是不确定的，不能简单的认为是取出第一条数据。

  1. select * from table_name order by col_name limit 1;

只有通过 order by 排序之后取出的数据，才是确定的。

创建表结构

官网文档：https://dev.mysql.com/doc/refman/5.7/en/create-table.html

临时表的创建

  1. --
  2. --  mysql 5.7.9
  3. --
  4. mysql> select version();
  5. +-----------+
  6. | version() |
  7. +-----------+
  8. | 5.7.9-log |
  9. +-----------+
  10. 1 row in set (0.00 sec)
  12. mysql> use burn_test;
  13. Database changed
  15. mysql> create temporary table temp_a(a int);
  16. Query OK, 0 rows affected (0.01 sec)
  18. mysql> show  create table temp_a\G
  19. *************************** 1. row ***************************
  20.        Table: temp_a
  21. Create Table: CREATE TEMPORARY TABLE `temp_a` (
  22.   `a` int(11) DEFAULT NULL
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4   -- 使用的是innodb 
  24. 1 row in set (0.00 sec)
  26. --
  27. -- mysql 5.6.27
  28. --
  29. mysql> select version();
  30. +------------+
  31. | version()  |
  32. +------------+
  33. | 5.6.27-log |
  34. +------------+
  35. 1 row in set (0.00 sec)
  37. mysql> create temporary table  temp_a_56(a int);
  38. Query OK, 0 rows affected (0.06 sec)
  40. mysql> show create table temp_a_56\G
  41. *************************** 1. row ***************************
  42.        Table: temp_a_56
  43. Create Table: CREATE TEMPORARY TABLE `temp_a_56` (
  44.   `a` int(11) DEFAULT NULL
  45. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  -- 5.6 也是innodb
  46. 1 row in set (0.00 sec)
  48. -----
  50. --
  51. -- 终端1 MySQL 5.7.9
  52. --
  53. mysql> select version();
  54. +-----------+
  55. | version() |
  56. +-----------+
  57. | 5.7.9-log |
  58. +-----------+
  59. 1 row in set (0.00 sec)
  61. mysql> show processlist\G
  62. *************************** 1. row ***************************
  63.      Id: 10  -- 当前ID 是 10
  64.    User: root
  65.    Host: localhost
  66.      db: burn_test
  67. Command: Query
  68.    Time: 0
  69.   State: starting
  70.    Info: show processlist   -- 当前终端执行
  71. *************************** 2. row ***************************
  72.      Id: 12
  73.    User: root
  74.    Host: localhost
  75.      db: NULL
  76. Command: Sleep
  77.    Time: 328
  78.   State: 
  79.    Info: NULL
  80. *************************** 3. row ***************************
  81.      Id: 13
  82.    User: root
  83.    Host: localhost
  84.      db: burn_test
  85. Command: Sleep
  86.    Time: 16
  87.   State: 
  88.    Info: NULL
  89. 3 rows in set (0.00 sec)
  91. mysql> insert into temp_a values(123);
  92. Query OK, 1 row affected (0.00 sec)
  94. mysql> select * from temp_a;      
  95. +------+
  96. | a    |
  97. +------+
  98. |  123 |
  99. +------+
  100. 1 row in set (0.00 sec)
  102. --
  103. -- 终端2 MySQL 5.7.9
  104. --
  105. mysql> select version();
  106. +-----------+
  107. | version() |
  108. +-----------+
  109. | 5.7.9-log |
  110. +-----------+
  111. 1 row in set (0.00 sec)
  113. mysql> show processlist\G
  114. *************************** 1. row ***************************
  115.      Id: 10
  116.    User: root
  117.    Host: localhost
  118.      db: burn_test
  119. Command: Sleep
  120.    Time: 75
  121.   State: 
  122.    Info: NULL
  123. *************************** 2. row ***************************
  124.      Id: 12
  125.    User: root
  126.    Host: localhost
  127.      db: NULL
  128. Command: Sleep
  129.    Time: 403
  130.   State: 
  131.    Info: NULL
  132. *************************** 3. row ***************************
  133.      Id: 13   -- 当前 ID 是 13
  134.    User: root
  135.    Host: localhost
  136.      db: burn_test
  137. Command: Query
  138.    Time: 0
  139.   State: starting
  140.    Info: show processlist  -- 当前终端执行
  141. 3 rows in set (0.00 sec)
  143. mysql> use burn_test;
  144. Database changed
  146. mysql> show tables;
  147. Empty set (0.00 sec)   -- 从其他终端登录的用户(session)无法看到temp_a这个临时表
  149. ----
  151. --
  152. -- 临时表 和 普通表 同名问题
  153. --
  154. mysql> create table test_1 (a int);  -- 创建一张普通的表叫做 test_1
  155. Query OK, 0 rows affected (0.16 sec)
  157. mysql> insert into test_1 values(23);
  158. Query OK, 1 row affected (0.03 sec)
  160. mysql> insert into test_1 values(24);
  161. Query OK, 1 row affected (0.03 sec)
  163. mysql> select * from test_1;
  164. +------+
  165. | a    |
  166. +------+
  167. |   23 |   -- 可以看到插入的数据
  168. |   24 |
  169. +------+
  170. 2 rows in set (0.00 sec)
  172. mysql> create temporary table test_1 (a int);   -- 创建一种和test_1 同名的临时表
  173. Query OK, 0 rows affected (0.00 sec)
  175. mysql> insert into test_1 values(1000);  -- 插入一个 不一样的值
  176. Query OK, 1 row affected (0.00 sec)
  178. mysql> select * from test_1;
  179. +------+
  180. | a    |
  181. +------+
  182. | 1000 |   -- 只能搜索到 临时表中的数据
  183. +------+
  184. 1 row in set (0.00 sec)
  186. mysql> create temporary table if not exists table_name (a int);  -- 使用if not exists进行判断
  • 临时表是 SESSION 级别的, 当前用户 logout 或者其他用户登录上来，是无法看到这张表的。
  • 当临时表和普通表同名时，当前用户只能看到同名的临时表。
  • 创建表时带上 if not exists 进行表的存在性检查；同时建议在临时表的表名前面加上统一的 prefix。

临时表的作用

临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的，不要和 SQL 优化器在排序过程中内部帮你创建的临时表相混淆。

比如在存储过程中，可以用来存储临时数据，存储过程执行结束后，释放临时表。

临时表的存储引擎

  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 5.7.9-log |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  9. mysql> show variables like "default%tmp%";
  10. +----------------------------+--------+
  11. | Variable_name              | Value  |
  12. +----------------------------+--------+
  13. | default_tmp_storage_engine | InnoDB |  -- 5.7.9 的临时表默认存储引擎就是 InnoDB
  14. +----------------------------+--------+
  15. 1 row in set (0.00 sec)
  18. mysql> select version();
  19. +------------+
  20. | version()  |
  21. +------------+
  22. | 5.6.27-log |
  23. +------------+
  24. 1 row in set (0.00 sec)
  26. mysql> show variables like "default%tmp%";
  27. +----------------------------+--------+
  28. | Variable_name              | Value  |
  29. +----------------------------+--------+
  30. | default_tmp_storage_engine | InnoDB |  -- 5.6.27 的临时表默认存储引擎也是 InnoDB
  31. +----------------------------+--------+
  32. 1 row in set (0.00 sec)
  34. -- 5.6 之前用的是MyISAM

临时表存储位置

  1. --
  2. -- mysql 5.7
  3. --
  4. mysql> system ls -l /tmp  # 使用system 可以解析执行linux shell命令
  5. total 20
  6. drwxr-xr-x. 4 mysql mysql 4096 Dec  2 10:06 mysql_data
  7. srwxrwxrwx. 1 mysql mysql    0 Dec  2 21:20 mysql.sock_56
  8. srwxrwxrwx. 1 mysql mysql    0 Dec  2 20:51 mysql.sock_57
  9. -rw-------. 1 mysql mysql    5 Dec  2 20:51 mysql.sock_57.lock
  10. -rw-r-----. 1 mysql mysql 8554 Dec  2 22:04 #sqlf18_a_0.frm  -- temp_1 的表结构
  12. mysql> system ls -l /data/mysql_data/5.7/ | grep ib
  13. -rw-r-----. 1 mysql mysql       879 Dec  2 20:47 ib_buffer_pool
  14. -rw-r-----. 1 mysql mysql  12582912 Dec  2 22:21 ibdata1
  15. -rw-r-----. 1 mysql mysql 134217728 Dec  2 22:20 ib_logfile0
  16. -rw-r-----. 1 mysql mysql 134217728 Dec  2 21:33 ib_logfile1
  17. -rw-r-----. 1 mysql mysql  12582912 Dec  2 22:33 ibtmp1  -- 这个是我们的表结构对应的数据
  19. mysql> show variables like "innodb_temp%";
  20. +----------------------------+-----------------------+
  21. | Variable_name              | Value                 |
  22. +----------------------------+-----------------------+
  23. | innodb_temp_data_file_path | ibtmp1:12M:autoextend |
  24. +----------------------------+-----------------------+
  25. 1 row in set (0.00 sec)
  27. -----
  29. --
  30. -- mysql 5.6
  31. --
  32. mysql> system ls -l /tmp
  33. total 68
  34. drwxr-xr-x. 4 mysql mysql  4096 Dec  2 10:06 mysql_data
  35. srwxrwxrwx. 1 mysql mysql     0 Dec  2 21:20 mysql.sock_56
  36. srwxrwxrwx. 1 mysql mysql     0 Dec  2 20:51 mysql.sock_57
  37. -rw-------. 1 mysql mysql     5 Dec  2 20:51 mysql.sock_57.lock
  38. -rw-rw----. 1 mysql mysql  8554 Dec  2 22:38 #sql13f3_7_0.frm   -- 表结构
  39. -rw-rw----. 1 mysql mysql 49152 Dec  2 22:38 #sql13f3_7_0.ibd   -- 表数据
  41. -- 5.6.27 中没有 innodb_temp_data_file_path 变量
  42. mysql> show variables like "innodb_temp%";
  43. Empty set (0.00 sec)
  45. mysql> show variables like "%innodb%temp%";
  46. Empty set (0.00 sec)
  • MySQL 5.7.9 把临时表结构放在 tmpdir，而数据表数据放在 datadir
  • MySQL 5.6.27 把临时表结构和表数据都放在 tmpdir

查看表结构

  1. mysql> show create table test_1\G   -- 表结构
  2. *************************** 1. row ***************************
  3.        Table: test_1
  4. Create Table: CREATE TABLE `test_1` (
  5.   `a` int(11) DEFAULT NULL
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  7. 1 row in set (0.00 sec)
  9. mysql> desc test_1\G  -- 表的描述,描述二维表信息
  10. *************************** 1. row ***************************
  11.   Field: a
  12.    Type: int(11)
  13.    Null: YES
  14.     Key: 
  15. Default: NULL
  16.   Extra: 
  17. 1 row in set (0.00 sec)
  19. mysql> show table status like "test_1"\G    -- 看表结构的元数据信息
  20. *************************** 1. row ***************************
  21.            Name: test_1
  22.          Engine: InnoDB
  23.         Version: 10
  24.      Row_format: Dynamic
  25.            Rows: 2
  26.  Avg_row_length: 4096
  27.     Data_length: 8192
  28. Max_data_length: 0
  29.    Index_length: 0
  30.       Data_free: 0
  31.  Auto_increment: NULL
  32.     Create_time: 2015-12-02 22:20:19
  33.     Update_time: 2015-12-02 22:20:44
  34.      Check_time: NULL
  35.       Collation: utf8mb4_general_ci
  36.        Checksum: NULL
  37.  Create_options: 
  38.         Comment: 
  39. 1 row in set (0.00 sec)

更新表结构

ALTER TABLE 官方文档：https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

  1. mysql> alter table test_1 add column b char(10);  -- 添加列 b
  2. Query OK, 0 rows affected (0.25 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> select * from test_1;
  6. +------+------+
  7. | a    | b    |
  8. +------+------+
  9. |   23 | NULL |
  10. |   24 | NULL |
  11. +------+------+
  12. 2 rows in set (0.00 sec)
  14. mysql> alter table test_1 drop column b;  -- 删除列 b
  15. Query OK, 0 rows affected (0.27 sec)
  16. Records: 0  Duplicates: 0  Warnings: 0
  18. mysql> select * from test_1;
  19. +------+
  20. | a    |
  21. +------+
  22. |   23 |
  23. |   24 |
  24. +------+
  25. 2 rows in set (0.00 sec)

注意，当表记录很大的时候，ALTER TABLE 会很耗时，影响性能。

MySQL 早期版本在执行 ALTER TABLE 的时候会锁住整张表，5.6 以后可以执行 Online DDL 操作，以提高性能，参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html，查看哪些操作可以执行 Online DDL。

Online DDL 表示在执行一些 DDL 操作的时候，表不会被锁住，不会阻塞应用，应用仍然可以执行读写操作。

  • DDL（Data Definition Language）：数据定义语言，管理表和索引结构。DDL 的最基本是 CREATE、ALTER、RENAME、DROP 和 TRUNCATE 语句。
  • DML（Data Manipulation Language）：数据操纵语言，是 SQL 用于添加、更新和删除数据的子集。

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/bboou6 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。