MySQL Meta 信息与 CREATE TABLE 的对应关系

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: # 前情提要 下面四篇文章是数月之前对length的理解 [MySQL Meta中的length字段 -- (1) 初始值的length计算](http://blog.csdn.net/maray/article/details/48657119) [MySQL Meta中的length字段 -- (2) length的推导](http://blog.csdn.net/maray/

前情提要

下面四篇文章是数月之前对length的理解

MySQL Meta中的length字段 -- (1) 初始值的length计算
MySQL Meta中的length字段 -- (2) length的推导
MySQL Meta中的length字段 -- (3) length的推导举例
MySQL Meta中的length字段 -- (4) 玩儿MySQL代码

温故而知新,几个月后,对 length 等 meta 信息的理解又进一步,本文再记之。

混乱的MySQL定义

用“混乱”这个词有点大言不惭,但是,MySQL通过SQL语句描述 Length、Scale、Precision、Display Width这几个概念,描述手法真的很混乱。

你能清楚地描述 Length、Scale、Precision、Display Width 四个概念吗?他们的区别与关联是什么?CREATE TABLE 语句如何表达这四个概念?

如果你能说清楚,就已经掌握,不用看本文了。说不清楚,就听我慢慢道来。

本文简单起见,只以 MySQL 的四大类数据类型作为范例讨论:

  • 整形(Int、TinyInt、MediumInt、BigInt)
  • 浮点(Float、Double)
  • 字符串(Binary、Char、VarBinary、VarChar)
  • 定点数(Decimal)

创建一个包含上面4中类型的表:

CREATE TABLE sample (
    i int(3),
    f double(4, 1),
    d decimal(6, 1),
    c varchar(5)
);
  • i int(3)

其中 3 仅仅表示display width,i 的 precision 为 11,也就是说,INSERT INTO sample (i) VALUES (1234567) 是没问题的,尽管1234567的位数超过了3位。它的 SCALE 为 0。它的 LENGTH 是 3,它的 LENGTH 是 3,它的 LENGTH 是 3(重要事情说三遍!!!),display width 等于 length。

mysql> select i from sample;
Field   1:  `i`
Catalog:    `def`
Database:   `test`
Table:      `sample`
Org_table:  `sample`
Type:       LONG
Collation:  binary (63)
Length:     3
Max_length: 7
Decimals:   0
Flags:      NUM

在这种情况下,如果客户端傻乎乎地按照Length的值分配内存,必将Buffer溢出。怎么办?客户端才不会相信服务器传来的这个值,它完全可以根据Type=Long来分配一个最大Buffer,确保任意整数都能放得下。

理解了上面的概念后,回去好好看看自己写的 Server 代码,是不是把 Length 和 Precision 的概念弄混了? 再来个例子加强理解:

mysql> INSERT INTO sample (i) VALUES (123456789012);
ERROR 1264 (22003): Out of range value for column 'i' at row 1
mysql> INSERT INTO sample (i) VALUES (1234567890);
Query OK, 1 row affected (0.01 sec)
  • f double(4, 1),

4 表示 precision,1 表示 scale,length 为4 + 1 + 1 = 6。其中 length 的算法为 precision + 小数点1位 + 符号位1位。 display width 有意义的前提是有 ZEROFILL 标记。如果有 ZEROFILL 标记,则自动为 UNSIGNED,所以 display width = precision + 小数点1位, length = precision + 小数点 1 位。

总结一下:没有ZEROFILL,LENGTH 有意义, DISPLAY WIDTH 无意义;有 ZEROFILL,LENGTH 和 DISPLAY WIDTH 相等。

mysql> INSERT INTO sample(f) VALUES (1234.1);
ERROR 1264 (22003): Out of range value for column 'f' at row 1
mysql> INSERT INTO sample(f) VALUES (123.1);
Query OK, 1 row affected (0.01 sec)
  • d decimal(6, 1),

因为 decimal 会用于金融领域,MySQL 那帮孙子也谨慎起来,把 Decimal 的行为实现得非常规范,所有的行为都可以解释。
6 表示 precision,1 表示 scale,Length = precision + 小数点1 + 符号位1 = 8。如果后面加上 ZEROFILL,则 Length = precision + 小数点 = 7, Display width = Length = 7。

下面专门写一个CASE来验证上面对Decimal的分析,同时证明 MySQL 那帮人的不严肃。按道理讲,Decimal 和 Double 在 precision、scale、length 上的表现行为应该是一致的,但实际偏偏不是,请看:

mysql> CREATE TABLE x_zf (f_zf double(6, 1) zerofill, d_zf decimal(6, 1) zerofill );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE x (  f double(6, 1),   d decimal(6, 1) );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO x_zf VALUES (1.1, 1.1);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO x VALUES (1.1, 1.1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM x_zf;
Field   1:  `f_zf`
Type:       DOUBLE
Length:     6
Decimals:   1
Flags:      UNSIGNED ZEROFILL NUM

Field   2:  `d_zf`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Decimals:   1
Flags:      UNSIGNED ZEROFILL NUM

+--------+---------+
| f_zf   | d_zf    |
+--------+---------+
| 0001.1 | 00001.1 |
+--------+---------+
1 row in set (0.00 sec)

看!除了Type不同,其余都一样,但计算出来的Length、填充的0的个数,居然不一样。没别的原因,MySQL 的浮点类型实现得不对。

mysql> SELECT * FROM x;
Field   1:  `f`
Type:       DOUBLE
Length:     6
Decimals:   1
Flags:      NUM

Field   2:  `d`
Type:       NEWDECIMAL
Length:     8
Decimals:   1
Flags:      NUM


+------+------+
| f    | d    |
+------+------+
|  1.1 |  1.1 |
+------+------+
1 row in set (0.00 sec)
  • c varchar(5)

precision、scale都无意义,默认为0,LENGTH=5,Display width无意义。

总结

  1. 字符类型在这四大类中是最简单的,其次是定点数,整形和浮点数都有点Bug,根本就无法“理解”
  2. Display Width 仅仅在有zerofill、且为基础列的时候有意义
  3. 当 Display Width 有意义的时候,Display Width = Length
  4. Length 总是有意义,它表示一个列转化成字符串时需要用多少个字节来容纳
  5. CREATE TABLE语句中的数字,对不同类型的意义不同:

    • 整形:display width
    • 浮点:precision、scale、display width、length
    • 定点:precision、scale、display width、length
    • 字符:length
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
关系型数据库 MySQL
MySQL查看连接数和进程信息
这篇文章介绍了如何在MySQL中查看连接数和进程信息,包括当前打开的连接数量、历史成功建立连接的次数、连接错误次数、连接超时设置,以及如何查看和终止正在执行的连接进程。
542 10
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
|
1月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
58 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL 如何存储地理信息
MySQL 如何存储地理信息
95 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
373 0
|
3月前
|
关系型数据库 MySQL Java
【Azure 应用服务】应用服务连接 Azure MySQL 一直失败,报错 Create connection error
【Azure 应用服务】应用服务连接 Azure MySQL 一直失败,报错 Create connection error
|
4月前
|
DataWorks 监控 关系型数据库
利用 DataWorks 数据推送定期推播 MySQL 或 StarRocks Query 诊断信息
DataWorks 近期上线了数据推送功能,能够将数据库查询的数据组织后推送到各渠道 (如钉钉、飞书、企业微信及 Teams),除了能将业务数据组织后推送,也能将数据库自身提供的监控数据组织后推送,这边我们就以 MySQL (也适用于StarRocks) 为例,定期推播 MySQL 的数据量变化等信息,帮助用户掌握 MySQL 状态。
105 1
|
4月前
|
SQL 安全 关系型数据库
MySQL创建视图(CREATE VIEW)13
【7月更文挑战第13天】创建视图是指在已经存在的 MySQL 数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
68 1
|
4月前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
|
4月前
|
关系型数据库 MySQL 数据库
Mysqlbug-Could not create or access the registry key needed for the MySQL applicationto, TIMESTAMP w
Mysqlbug-Could not create or access the registry key needed for the MySQL applicationto, TIMESTAMP w