4.MySQL数据类型

目录

数据类型

​编辑数值类型

tinyint类型

bit类型

float类型

decimal类型

字符串类型

char类型

varchar

varchar和char的区别

日期和时间类型

---

数据类型

数值类型

说明一下：MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

tinyint类型

数值越界测试

mysql> create table t1 (num tinyint);
Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(127);
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from t1;
+------+
| num  |
+------+
|    1 |
|    2 |
|  127 |
+------+
3 rows in set (0.00 sec)mysql> 

带符号的范围是-128~127，无符号的范围0-255，默认有符号 

有符号的只要插入的范围在-128~127之间的都不会报错，如果插入的范围大于这个区间就会报错。

128不在这个区间，所以不会显示，插入报错了。

tinyint类型（无符号）

mysql> create table t1 (num tinyint unsigned);
ERROR 1050 (42S01): Table 't1' already exists
mysql> create table t2(num tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)mysql> insert into t2 values(128);
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values(129);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(254);
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values(255);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values(256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(0);
Query OK, 1 row affected (0.00 sec)

无符号的插入范围是在0~255之间 ，在该范围内都可以进行插入，不在这个范围之间不能插入。

建议：除非场景要求数值类型必须是无符号，否则尽量不要使用无符号，因为有符号的数值类型存不下的数据，其对应的无符号类型同样可能存不下，这时应该直接将数值类型进行提升。

bit类型

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1

举例：

mysql> create table t3(id int,a bit(8));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(10,10);
Query OK, 1 row affected (0.00 sec)mysql> select *from t3;
+------+------+
| id   | a    |
+------+------+
|   10 | |
+------+------+
1 row in set (0.00 sec)

发生了一个很奇怪的现象，a的数据10没有出现？？？？？？？？

那接下来看下面这个例子：

咦咦咦？？？为什么插入了65会显示A呢？？

根本原因是因为bit类型在显示时，是按照ASCII码对应的值进行显示的，而在ASCII码表中10对应的是控制字符LF，表示换行的意思。如果向表中插入记录时指定id和a的值均为65，由于ASCII码表中65对应的是字符A，因此插入记录后查看表就会发现a的值显示的是A。如下：

bit类型测试

如果我们有个这样的值，只能存放0 1 ，这个时候我们就可以定义bit(1),这样还可以节省空间。

mysql> create table t4(-> gender bit(1)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into t4 values(0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t4 values(1);
Query OK, 1 row affected (0.01 sec)mysql> select * from t4;
+--------+
| gender |
+--------+
|        |
|       |
+--------+
2 rows in set (0.00 sec)

当我们插入不是0 1 的数据时

• 虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。
• 因为查询位类型数据时，默认会按照ASCII码对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。

float类型

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

案例：

小数：float(4,2)表示范围是-99.99~99.99,MySQL在保存值时会进行四舍五入。

此外，由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。如下：

问题：当我们的float(4,2)如果是一个有符号的，则表示范围是-99.99~99.99,如果float(6,3)请问是多少呢？？

当然和大家想的一样就是999.999~999.999

float无符号

mysql> create table t6(salary float(4,2) unsigned);
Query OK, 0 rows affected (0.02 sec)mysql> insert into t6 values(0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(-2);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 values(3);
Query OK, 1 row affected (0.01 sec)mysql> insert into t6 values(99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 values(99.994);
Query OK, 1 row affected (0.00 sec)mysql> select *from t6;
+--------+
| salary |
+--------+
|   0.00 |
|   3.00 |
|  99.99 |
+--------+
3 rows in set (0.00 sec)

无符号float类型的取值范围，实际就是把对应有符号float类型中的负数部分拿走了，因此float(4,2)的取值范围为0~99.99，实际可插入的范围是0~99.994 

decimal类型

• decimal(5,2) 表示的范围是 -999.99 ~ 999.99
• decimal(5,2) unsigned 表示的范围 0 ~ 999.99
• decimal和float很像，但是有区别:
• float和decimal表示的精度不一样

说明： float 表示的精度大约是 7 位。

decimal 整数最大位数 m 为 65 。支持小数最大位数 d 是 30 。如果 d 被省略，默认为 0. 如果 m 被省略，

默认是 10 。

建议：如果希望小数的精度高，推荐使用decimal。

字符串类型

char类型

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

mysql> create table t8( char1 char(2) );
Query OK, 0 rows affected (0.02 sec)mysql> insert into t8 values('ab');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values('hwh');
ERROR 1406 (22001): Data too long for column 'char1' at row 1
mysql> insert into t8 values('h');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 values('111');
ERROR 1406 (22001): Data too long for column 'char1' at row 1
mysql> insert into t8 values('11');
Query OK, 1 row affected (0.00 sec)

说明：

char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过 2 个， 最多只能是 255

mysql> create table tt10(id int ,name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use
BLOB or TEXT instead

在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了

varchar

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

关于 varchar(len),len 到底是多大，这个 len 值，和表的编码密切相关：

varchar 长度可以指定为 0 到 65535 之间的值，但是有 1 - 3 个字节用于记录数据大小，所以说有效字节数是65532 。

当我们的表的编码是 utf8 时， varchar(n) 的参数 n 最大值是 65532/3=21844[ 因为 utf 中，一个字符占用3 个字节 ] ，如果编码是 gbk ， varchar(n) 的参数 n 最大是 65532/2=32766 （因为 gbk 中，一个字符占用2 字节）。

varchar和char的区别

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

日期和时间类型

常用的日期有如下三个：

• date :日期 'yyyy-mm-dd' ，占用三字节
• datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

案例：

添加数据时，时间戳自动补当前时间

更新数据：时间戳也会自动更新