MySQL

MySQL:简介 Ubuntu 安装 MySQL MySQL:官方文档地址 MySQL:DDL、DQL、DML、DCL的含义 MySQL:Mac 中启动 关闭 MySQL 服务 MySQL:有哪些好用的管理工具? MySQL:命令行工具 mycli MySQL:CHAR类型 MySQL:VARCHAR类型 MySQL:整型数字 MySQL:datetime 类型 MySQL:时间戳 MySQL:创建和删除数据库 MySQL:切换和查看数据库 MySQL:创建和删除表 MySQL:在表中增加、删除、修改列 MySQL:创建和删除主键 MySQL:使用 rename 修改表名 MySQL:修改自增主键id的类型 MySQL:如何创建一个相同的表 MySQL:修改表的字符编码 MySQL:增删查改 MySQL:插入数据 MySQL:插入多行数据 MySQL:使用 insert set 插入数据 MySQL:大小写和反引号 MySQL:字符串类型值的大小写 MySQL:SQL注释 MySQL:不要使用utf8 MySQL:NULL的判等 MySQL:InnoDB存储引擎的限制 MySQL:if和case的使用 MySQL:使用 load data 快速导入数据 MySQL:使用 select into outfile 导出数据 MySQL:查询和设置 sql_mode MySQL:严格模式 MySQL:NOT NULL 字段不插入数据,会发生什么? MySQL:无符号整数列插入负数会发生什么? MySQL:关于 null 的那些事 MySQL:大表行数查询 MySQL:自动生成创建时间、更新时间;自动更新更新时间 MySQL:insert ignore MySQL:字符集排序规则 MySQL:如果连续更新一个字段两次,结果是? MySQL:字符串转数字 MySQL:尾部空格 MySQL:添加和删除索引 MySQL:唯一索引与NULL MySQL:唯一索引的单列长度限制 MySQL:InnoDB 索引 MySQL:字符集排序规则对唯一索引的影响 MySQL:唯一索引冲突消耗主键 ID MySQL 使用 index hint 指定索引:ignore index、force index、use index MySQL:查看客户端连接信息 MySQL:查看表的状态 show table status MySQL:如何治理连接数 ? MySQL:如何监控和处理慢查询与长事务 ? MySQL:自定义函数 MySQL:now() 函数 MySQL:unix_timestamp() 函数 MySQL:from_unixtime() 函数 MySQL:version() 函数 MySQL:current_timestamp() 函数 MySQL:cast 函数 MySQL:convert 函数 MySQL:使用 greatest、least 函数获取行最大值、最小值 MySQL:使用 group_concat 函数连接多行数据为一个字符串 MySQL:获取版本号 MySQL:Java 类型映射 MySQL下创建只能有一行记录的table 关于MySQL的字符集 理解数据库中的undo日志、redo日志、检查点 ubuntu下源码安装MySQL MySQL:JOIN解惑 如何快速更新数据库中的百万条数据

MySQL:整型数字


#MySQL


数据类型官方文档: https://dev.mysql.com/doc/refman/5.6/en/data-types.html 。

基本特性

各种类型的存储大小、最大最小取值范围如下:

类型 存储大小(字节) 有符号类型最小值 有符号类型最大值 无符号类型最小值 无符号类型最大值
TINYINT 1 -128 127 0 255
SMALLINT 2 -32768 32767 0 65535
MEDIUMINT 3 -8388608 8388607 0 16777215
INTINTEGER 4 -2147483648 2147483647 0 4294967295
BIGINT 8 -263 2^63-1 0 2^64-1

int(m) 代表显示长度为m。例如 int(1) 代表显示长度为1,但若值的长度大于1,例如为99,也会显示为 99 。

这个显示长度, 是客户端的行为。因为是显示长度,所以不影响数值的大小。

默认值

create table test_table (
    num int
) engine = InnoDB character set = utf8mb4;

查询创建语句:

mysq> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `num` int(11) DEFAULT NULL            |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

可以看到默认值是 null。

看下效果:

insert into test_table values();
insert into test_table(num) values(default);
mysql> select * from test_table;
+--------+
| num    |
+--------+
| <null> |
| <null> |
+--------+

若指定列为 not null 呢?

mysql> create table test_table (num int not null) engine = InnoDB character set = utf8mb4;

mysql> show create table test_table
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `num` int(11) NOT NULL                |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

mysql> insert into test_table values();
mysql> insert into test_table(num) values(default);

mysql> select * from test_table;
+-----+
| num |
+-----+
| 0   |
| 0   |
+-----+

可以看到,用0做默认值。

默认的显示长度

create table test_table (
    num int
) engine = InnoDB character set = utf8mb4;
mysq> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `num` int(11) DEFAULT NULL            |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

有符号int的默认显示长度是11,-2147483648的长度是11,所以显示长度把负号也算上了。

如果是无符号的int呢?

create table test_table (
    num int unsigned
) engine = InnoDB character set = utf8mb4;
mysql> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `num` int(10) unsigned DEFAULT NULL   |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

无符号int的默认显示长度是10,因为最小值是0,最大值4294967295的长度是 10。

显示长度与zerofill

这个显示长度, 是存在MySQL服务器中,当有查询操作时,会把这个属性返回给进行查询的客户端,客户端据此进行展示。有些客户端会忽略该属性。很多情况下是看不出有什么效果的,加上zerofill后,一些客户端中会看到效果。zerofill用来修饰数字类型(int、float等),作用是显示时若长度不够,则在前面补0。它有一个副作用,会将列类型变成 unsigned 。所以要慎用!

另外,https://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html 给出了显示长度和zerofill 另外两个特点:

  1. The ZEROFILL attribute is ignored when a column is involved in expressions or UNION queries. 含义:在表达式或者 UNION 查询时,zerofill 不会生效。
  2. If you store values larger than the display width in an integer column that has the ZEROFILL attribute, you may experience problems when MySQL generates temporary tables for some complicated joins. In these cases, MySQL assumes that the data values fit within the column display width. 含义:如果存储的数字大于显示长度,当因为复杂的join查询导致临时表产生,可能会出现不符合预期的结果。-》这又是一个副作用。

我们忽略zerofill 的副作用,通过示例看下效果:

创建表:

create table test_table (
    num1 int(4) zerofill,
    num2 int(10) zerofill
) engine = InnoDB character set = utf8mb4;

查看创建语句,会发现num1和num2变成无符号类型了。

mysql> show create table test_table;
+------------+-------------------------------------------------+
| Table      | Create Table                                    |
+------------+-------------------------------------------------+
| test_table | CREATE TABLE `test_table` (                     |
|            |   `num1` int(4) unsigned zerofill DEFAULT NULL, |
|            |   `num2` int(10) unsigned zerofill DEFAULT NULL |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4         |
+------------+-------------------------------------------------+

插入数据:

insert into test_table(num1, num2) values(1, 2);
insert into test_table(num1, num2) values(12345, 2);

用 MySQL 自带的命令行客户端 mysql 查询数据:

mysql> select * from test_table;
+-------+------------+
| num1  | num2       |
+-------+------------+
|  0001 | 0000000002 |
| 12345 | 0000000002 |
+-------+------------+

可以看到,长度不够的数字前面补了0。

用第三方命令行客户端 mycli 查询数据,效果不同:

mysqlt> select * from test_table;
+-------+------+
| num1  | num2 |
+-------+------+
| 1     | 2    |
| 12345 | 2    |
+-------+------+

这证明了显示长度和zerofill,不同客户端的处理方式不同。

更好的显示长度实现

首先:不要在创建表时指定显示长度,也不要使用 zerofill 关键词。为什么,因为有副作用。

那么有什么其他的方案可以达到相同的显示效果呢?

**方案1:**交给业务方。比如我们Java编写了一个web版的数据库管理系统,展示成什么样子,完全可在 Java 从数据库中取出数据后,Java 处理一下,再传给前端。例如 Java 将 1 转换成字符串 00001返回给前端。或者让前端代码将1转换成00001。

**方案2:**查询语句中用 LPAD 和 CAST 函数。

create table test_table (
    num int unsigned
) engine = InnoDB character set = utf8mb4;

insert into test_table(num) values(2);
mysql> select LPAD(CAST(num AS char(10)), 10, '0') as num from test_table
+------------+
| num        |
+------------+
| 0000000002 |
+------------+

( 本文完 )