mysql里面integer默认宽度_MySQL中关于数据类型指定宽度之后的情况

最新推荐文章于 2023-06-08 16:43:52 发布

饶毅

最新推荐文章于 2023-06-08 16:43:52 发布

阅读量933

点赞数

文章标签： mysql里面integer默认宽度

本文链接：https://blog.csdn.net/weixin_42416490/article/details/113191509

版权

概述

MySQL有很多种数据类型，最常用的就是int，char，varchar，这些类型在创建表的时候都可以指定该字段的宽度，方法是在类型后面加一个括号，括号中写宽度就可以了。

但是，在指定宽度之后，有时候，我们可以看到插入的数据有一些被截断了；有一些并没有截断，而是四舍五入了，甚至什么操作都没有，原样插入了。

下面对于每一种数据类型单独测试：

数字型(int、tinyint...)

mysql> create table t (id int(5));

mysql> insert into t values(1234567),(123),(12345);

mysql> select * from t;

+---------+

| id |

+---------+

| 1234567 |

| 123 |

| 12345 |

+---------+

从上面的例子中可以看到，对于int而言，虽然指定了宽度，但是当插入的数据宽度大于指定的宽度时，并不会截断。

其实对于int而言，要指定宽度，那么就必定要指定zerofill，但同样，zerofill只是在宽度不够的时候用0填充，但是宽度大于指定宽度时，数据仍然不会被截取。

mysql> create table t (id int(5) zerofill);

mysql> insert into t values(1234567),(123),(12345);

mysql> select * from t;

+---------+

| id |

+---------+

| 1234567 |

| 00123 |

| 12345 |

+---------+

字符串型(char、varchar)

mysql> create table t (fields_1 char(5),fields_2 varchar(5));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values("123","123"),("12345","12345"),("1234567","1234567");

Query OK, 3 rows affected, 2 warnings (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 2

mysql> show Warnings;

+---------+------+-----------------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------------+

| Warning | 1265 | Data truncated for column 'fields_1' at row 3 |

| Warning | 1265 | Data truncated for column 'fields_2' at row 3 |

+---------+------+-----------------------------------------------+

2 rows in set (0.00 sec)

mysql> select fields_1,length(fields_1),fields_2,length(fields_2) from t;

+----------+------------------+----------+------------------+

+----------+------------------+----------+------------------+

| 123 | 3 | 123 | 3 |

| 12345 | 5 | 12345 | 5 |

+----------+------------------+----------+------------------+

可以看到，对于char和varchar，如果制定了宽度，如果要插入的字符串的宽度超过了指定的宽度，则会截取掉超出的部分。

简单来说，varchar的可变长度，这个可变，前提是存入的字符串长度不超过定义该字段时指定的长度，如果长度超过了指定长度，即使是可变长度字符串类型，数据仍会出现截断。

可以简单记为：可缩不可扩。

而固定长度的char类型，在存储效率比varchar高，但是，会存在空间浪费的情况，所以空间利用率没有varchar高，而varchar是可变长度的，就意味着，在读数据的时候，效率没有char类型高，因为在读数据的时候要判断是否读到结尾。

拓展1

前面已经提到，对于数值类型的字段后面的宽度来说，只有在指定zerofill的时候，后面指定的宽度才有意义，否则，既不会出现截断，也不会出现0填充。那么，不会出现截断，是不是说，向一个int(5)字段的插入一个值，这个值是12345678912345678912345678...(由100位数字长度)，那么还能存进去吗？

看下面示例：

mysql> create table t (id int(5) zerofill);

Query OK, 0 rows affected (0.10 sec)

mysql> insert into t values (9999999999999999999999999999999999999999999);

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;

+---------+------+---------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------+

| Warning | 1264 | Out of range value for column 'id' at row 1 |

+---------+------+---------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from t;

+------------+

| id |

+------------+

| 4294967295 |

+------------+

1 row in set (0.00 sec)

可以从警告信息和执行结果中看出，当尝试向指定宽度字段插入一个很大的数据，大到远超该数据类型的上限，执行虽然会出现警告，但是，数据确实插入了，只不过存储的数据不是插入的数据，而是存了一个该类型的最大值。

所以可以得出结论：对一个数字类型的字段而言，其数据类型已经限定了它的数据范围，当尝试插入一个超过数据范围的值时，会触发警告，同时，存入该数据类型的最大值。

拓展2

前面也提到了字符串(char和varchar)后面指定的宽度，这个宽度就不像数字类型的宽度了，因为，如果是字符串类型，那么，一旦超过字符串后面指定的宽度，那么一定会出现截断。

这里有个问题，字符串后面指定的宽度，比如char(5)，varchar(5)，这个5是指5个字符，还是指5个字节呢，或者说是5个bit(位)呢？

前面的示例中，很显然看出，这个5不可能是bit(位)，毕竟一个字节就有8位，在测试中，一个字符都插不进去。

那么，要么是5个字符，或者5个字节。可能你会疑惑，5个字符和5个字节有什么区别吗？abc，是3个字符，同时也是3个字节，何必去区分呢？

那你想一下，咱们的汉字，一个汉字，经过不同的编码(GBK，GB2312，lantin1，UTF-8，UTF-8mb4)之后，所占的字节数是不一定相同的呀。

mysql> create table t (field char(5));

Query OK, 0 rows affected (0.40 sec)

mysql> show create table t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`field` char(5) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> insert into t values ('abcde');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('中国你好啊');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;

+---------+------+-----------------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------------------------------------------------+

| Warning | 1300 | Invalid utf8 character string: 'D6D0B9' |

| Warning | 1366 | Incorrect string value: '\xD6\xD0\xB9\xFA\xC4\xE3...' for column 'field' at row 1 |

+---------+------+-----------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

从上面的实例，很明显可以看出答案，char(5)后面的5，是指的5字节，而不是5个字符。

可以查看一下，存入的内容是什么：

mysql> select * from t;

+-------+

| field |

+-------+

| abcde |

| ????? |

+-------+

2 rows in set (0.03 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;

+-------+

| field |

+-------+

| abcde |

| ????? |

+-------+

2 rows in set (0.00 sec)

可以看出，后面虽然插入的“中国你好啊”，但是存的时候，已经出现乱码了，即使强制指定字符集，也是显示乱码。

咱们一般使用的都会utf8或者utf8mb4，可以在创建表格的时候，指定default charset=utf8。

拓展3

如果一个汉字使用某种编码方式(比如utf8)，在存储的时候占3字节，那么两个汉字，就需要6个字节来存。

那么，如果char(5)类型的字段，能存入“中国”两个字吗？中国两个字编码之后是6字节。

首先解决一个问题：

mysql> create table t ( field char(5)) default charset=utf8;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into t values ('abcde');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('中');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;

+---------+------+----------------------------------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------------------------------+

| Warning | 1300 | Invalid utf8 character string: 'D6D0' |

| Warning | 1366 | Incorrect string value: '\xD6\xD0' for column 'field' at row 1 |

+---------+------+----------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from t;

+-------+

| field |

+-------+

| abcde |

| |

+-------+

2 rows in set (0.00 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+-------+

| field |

+-------+

| abcde |

| |

+-------+

2 rows in set (0.00 sec)

可以看到，即使单个汉字“中”编码之后3字节(未超过5字节范围，仍旧未存入)，这时可以看一下数据库的字符集：

mysql> show variables like '%char%';

+--------------------------+-----------------------------------+

| Variable_name | Value |

+--------------------------+-----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | E:\phpStudy\MySQL\share\charsets\ |

+--------------------------+-----------------------------------+

8 rows in set (0.00 sec)

可以看到，character_set_database设定的还是latin1字符集编码，可以使用下面的命令修改：

set character_set_database='utf8';

接下来，测试过程中，即使插入单独的一个汉字，也会出现警告，并且查看插入的值，只出现一个?或者一些乱码，甚至没有值(空的)。

我尝试在php中执行插入和查看操作：

$mysqli = new Mysqli();

$mysqli->connect("localhost","root","root","test");

$mysqli->set_charset("utf8");

$mysqli->query("truncate table t");

$mysqli->query("insert into t values ('abcde')");

$mysqli->query("insert into t values ('你')");

$mysqli->query("insert into t values ('你好')");

$sql = "select * from t";

$mysqli_result = $mysqli->query($sql);

$res = $mysqli_result->fetch_all();//一次性去的所有数据

print_r($res);

执行之后，结果如下：

λ php index.php

Array

(

[0] => Array

(

[0] => abcde

)

[1] => Array

(

[0] => ?

)

[2] => Array

(

[0] => ???

)

至于为什么会这样，现在还没找到问题根源。之前好像也没遇到过这种情况呀，等有时间在其他机器上试一下。

饶毅

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql里面integer默认宽度_MySQL中关于数据类型指定宽度之后的情况

概述MySQL有很多种数据类型，最常用的就是int，char，varchar，这些类型在创建表的时候都可以指定该字段的宽度，方法是在类型后面加一个括号，括号中写宽度就可以了。但是，在指定宽度之后，有时候，我们可以看到插入的数据有一些被截断了；有一些并没有截断，而是四舍五入了，甚至什么操作都没有，原样插入了。下面对于每一种数据类型单独测试：数字型(int、tinyint...)mysql> c...
复制链接

扫一扫