MySQL字段类型的长度的含义、类型后面的数字的含义

MySQL字段类型的长度的含义、类型后面的数字的含义

---

0. MySQL字段类型的长度M

使用SQL即创建表的时候，有时候会在字段的类型后面加一个括号，括号里面有一个数字，如：

create TABLE pet(
                   name VARCHAR(20),
                   owner VARCHAR(21),
                   specise VARCHAR(22),
                   sex CHAR(1),
                   brith DATAE,
                   death DATE );

这里称这个数值为M，如上面的20、21、22和1我将其称为M。关于M的使用，官方文档的说法（参考自CSDN博主）：

Data type descriptions use these conventions:
For integer types, M indicates the maximum display width. For floating-point and fixed-point types, M is the total number of digits that can be stored (the precision). For string types, M is the maximum length. The maximum permissible value of M depends on the data type.
D applies to floating-point and fixed-point types and indicates the number of digits following the decimal point (the scale). The maximum possible value is 30, but should be no greater than M−2.
fsp applies to the TIME, DATETIME, and TIMESTAMP types and represents fractional seconds precision; that is, the number of digits following the decimal point for fractional parts of seconds. The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

我的理解（翻译）是：

• 对于整型来说，M是值最大的显示宽度（但编程语言读出的值不会受影响，但是这个显示宽度除了navicat中有用外，还有什么情况下有用？——这个暂未可知）。对于浮点类型和固定小数点类型来说，M是指能被存储的数字的总数（我个人实测并不是这样的，测试不出来对于float和double来说，M意味着什么，有知道的朋友还请留言指教），即精度

这个英文看了，实际上还是不完全懂M的含义。对于字符串类型，M是最大的长度。但是，M的最大数值仍取决于数据类型本身，你不能指定M的最大数值超过数据类型本身的限制。

---

这里还可以直接参考网友的总结点击
最后个人的总结：

• 对于INT型，M只是限制了该字段的显示宽度，显示的字符数。
• 对于char型，M指定了不超过char允许的范围内的该字段的字符数，存储的时候必然按M个字符进行存储，不够的时候用空格补。在检索的时候（我理解是查询的时候），取出的值尾部空格被删除，存储的时候尾部的空格可以认为没有做处理。
• 对于varchar型，M指定了不超过varchar允许的字符数的最大字符数为M，存储的时候以实际长度进行存储但这个长度不能超过M。然后与char的区别在于，不会在写入和读出的时候，对字符串的尾部空格进行删除。
• 对于char和varchar，前者注重效率，但是对于变动字符数的字符串，存在空间浪费的情况；后者varchar注重存储空间的占用，但是效率比char低。
• float型的M，这里具体用法确实还不知道。

1. char型的M

对于char型M指定了存储允许的最大字符个数而非显示的字符数，但由于其本身长度是允许在0-255个字节之间，因此M必须在这个范围内——char(257)会报错，char(0)不会报错但只允许插入空字符或者为NULL，然后不写M时默认是M(1)。以下参考网友的总结：
CHAR(M)定义的列的长度为固定的，M取值可以为0-255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字符，都要占去10个字符的空间,不足的自动用空格填充。

以下是一些测试

CREATE TABLE testM (
    char1 char(1),
    chardefault char,
    char20 char(20),
    intdef int(4),
    varchar1 varchar(1),
    varchar5 varchar(5)
);

mysql> DESCRIBE testm;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| char1       | char(1)    | YES  |     | NULL    |       |
| chardefault | char(1)    | YES  |     | NULL    |       |
| char20      | char(20)   | YES  |     | NULL    |       |
| intdef      | int        | YES  |     | NULL    |       |
| varchar1    | varchar(1) | YES  |     | NULL    |       |
| varchar5    | varchar(5) | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+

并且，对于char(1)，在INSERT时只允许一个字节，超过的时候会报错。因此可以确定，对于char来说，M不是指显示的字符数，而是允许的字符数。——不确定是字符还是字节——用偏僻汉字试了一下，应该是字符数，根据网友的总结，确实是字符而非字节，点我看《参考》。

---

对于char型字段，关于M 的作用，我的测试结论：

• M值限定了char型字段存储的字符串的字符个数小于等于M，当M=0时，仅能存储空字符或者为NULL，不知道能不能存储NULL
• M值和navicat中的长度是对应的，一样。
• 当INSERT的字符串长度大于M，那么仅保留M个字符，保留SQL中引号内的从左往右的前M个字符。

我认为varchar的M和char的M的含义和用法是一样的，因此没有测试。

---

2. varchar的M

对于varchar型，必须后面带括号，然后用法应该和char一样，似乎只是底层不太一样，根据菜鸟教程的说法，“它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换”。：

CREATE TABLE testM1 (
     char1 char(1),
     chardefault char,
     char257 char(20),
     intdef int,
     varchar11 varchar(1),
      varchardef VARCHAR
     );
//上面的SQL会报错，主要是VARCHAR后面没有带括号和数字
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 8

3. INT型的M

int型后面跟M最大不能超过255（实测不能超过255，对于网上说的超过4294967295，我表示表示疑问），而且在允许的范围内，无论你设置多少（int(0)也不会报错），在describe中int型字段都不显示这个M值，然后实测发现，对于int型，这个M似乎没用，因为设置了int(4)，存储的数值远大于四位数，且SELECT查询出来显示的数值也不只是四位：

mysql> CREATE TABLE testMint4 (
    ->     char1 char(1),
    ->     chardefault char,
    ->     char257 char(20),
    ->     intdef int(0),
    ->     varchar11 varchar(1)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> describe testmint4;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| char1       | char(1)    | YES  |     | NULL    |       |
| chardefault | char(1)    | YES  |     | NULL    |       |
| char257     | char(20)   | YES  |     | NULL    |       |
| intdef      | int        | YES  |     | NULL    |       |
| varchar11   | varchar(1) | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

另外，对于int来说，这个M值用于不限定该字段存储的数据的最大值，这里贴一个网友的总结来源：

1、整数型的数值类型已经限制了取值范围，有符号整型和无符号整型都有，而M值并不代表可以存储的数值字符长度，它代表的是数据在显示时显示的最小长度；

2、当存储的字符长度超过M值时，没有任何的影响，只要不超过数值类型限制的范围；

3、当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。
总结：int(11)，tinyint(1)，bigint(20)，后面的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。

---

但是，似乎网友的总结有误（关于zerofill部分），关于M值对于int型的作用，以下是我的实际测试的结论：

• 对于整型来说，后面括号里面的数字M和navicat表设计中的长度相等
• 对于Int型来说，不填写这个M值，那么默认的长度是11。
• 即便设置为int(1)、int(1)或int(64)，也不影响这个字段在MySQL中存储的数值范围-2147483648 ~ 2147483647（即-2³¹-1~2³¹-1）
• M为0的结果是，设置不生效，即最后变成默认的11
• 在Navicat中，设置了M为1时，查询出的结果显示的是数值的最高三位；M为4时，显示的是6位；根据规律推测，M和navicat中显示的数值的位数有关，显示位数=M+2，不包括负号（负号不占用位数）。
• 即便无法全部显示数值的各个数位，但是不影响该字段存储能力，即参考上面第三个结论。
• 关于zerofill：主要是数值位数不满M时，自动在最高位前面补0凑够M个数位，不影响值的存取。然后加入数值的数位大于M，则只会显示M+2个数位。相当于当数值的数位大于M的时候，这个zerofill不生效了
• zerofill的用法：指定zerofill后，会变成 int(M) unsigned zerofill，即变成了无符号类型

ALTER INTO table_name ADD COLUMN field_zerofill int(4) zeroflii

---

4. FLOAT型的M

M最大值为4294967295，与INT一样。
经过测试：

• float类型使用INSERT进行插入操作时，似乎只保留精确到小数点后的第五位。
• float(0）不会报错。
• 当M大于24时，会显示位double；M不能大于53，不知道为什么。
• For floating-point and fixed-point types, M is the total number of digits that can be stored (the precision).初步判断，和精度有关。具体暂不深入研究。
• 仅能确定的是，M对于数据的存储似乎没有太多影响，float型的数据似乎始终只能保持6位有效数据。

5. FLOAT型的M和D

另外，有一种格式是float(M,D)，例如：

CREATE TABLE test (
	score float(255,30)
)

即类型后面跟两个数255和30，我称之为M和N。根据博主ispotu-（点我跳转）的说法，M限定了本字段存储的数值的整数部分的位数为（M-D），D限定了小数位数。
下面是我个人测试的结论：

• M和D的大小有什么限制没有，没有深入测试，但初步确认，M不能超过255，然后D不能超过30
• M要大于D
• M设为255时，如float(255,30)，此时也不会变成double类型。
• 不管设的M和D是多少，字段的值始终受其是float或者double或者decimal的限制，即精度始终是被类型本身的限制限定的，并不会因为修改M和D二变化。