【MySQL】数据类型的注意点和应用

📢博客主页：https://blog.csdn.net/2301_779549673
📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
📢本文由 JohnKi 原创，首发于 CSDN🙉
📢未来很长，值得我们全力奔赴更美好的生活✨

文章目录

🏳️‍🌈一、数据类型分类

MySQL

数据类型在数据库中起着至关重要的作用。它决定了数据在数据库中的存储方式和可进行的操作。合理选择数据类型能够带来多方面的好处。

🏳️‍🌈1.1 tinyint 类型

我们先按照下面步骤创建一个数据表，只存储

tinyint

类型的数据

// 创建数据库 test_db
mysql> create database test_db;
Query OK,1 row affected(0.00 sec)// 切换当前使用的数据库为 test_db
mysql> use test_db;
Database changed

// 在括号内定义表 t1 的列结构，这里只定义了一列，列名为num，其数据类型被指定为tinyint
mysql> create table ifnot exists t1(-> num tinyint
    ->);
Query OK,0 rows affected(0.02 sec)

// 查看指定数据表（这里是t1表）的结构信息
mysql> desc t1;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| num   |tinyint(4)| YES  ||NULL||+-------+------------+------+-----+---------+-------+1 row in set(0.00 sec)// 查看当前所在数据库（之前通过use test_db切换到了test_db数据库）中包含哪些数据表
mysql> show tables;+-------------------+| Tables_in_test_db |+-------------------+| t1                |+-------------------+1 row in set(0.00 sec)// 查看创建指定数据表（这里是t1表）时使用的完整CREATE TABLE语句
mysql> show create table t1;+-------+------------------------------------------------------------------------------------------+| Table | Create Table                                                                             |+-------+------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (
  `num` tinyint(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------+1 row in set(0.00 sec)

我们已知

tinyint'

类型的数据存储范围为

[-128, 127]

往这个数据表中存储数据，看看反应

mysql> insert into t1 value(-128);
Query OK,1 row affected(0.00 sec)

mysql> insert into t1 value(127);
Query OK,1 row affected(0.01 sec)

mysql> insert into t1 value(129);
ERROR 1264(22003): Out of range value for column 'num' at row 1
mysql> insert into t1 value(0);
Query OK,1 row affected(0.00 sec)

mysql> insert into t1 value(1);
Query OK,1 row affected(0.00 sec)

mysql> insert into t1 value(-1);
Query OK,1 row affected(0.00 sec)

mysql> desc t1;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| num   |tinyint(4)| YES  ||NULL||+-------+------------+------+-----+---------+-------+1 row in set(0.00 sec)

mysql> select * from t1;+------+| num  |+------+|-128||127||0||1||-1|+------+5 rows in set(0.01 sec)

在插入

 -128、127、0、1、-1

时都是正确的，而且最后也成功打印出来，证明存进去了

但是插入

129

时显示错误，超出数据范围，并且也没有打印出来

所以我们可以得出结论

1. 如果我们向mysq!特定的类型中插入不合法的数据，MySQL一般都是直接拦截我们，不让我们做对应的操作!
2. 反过来，如果我们已经有数据被成功插入到mysql中了，一定插入的时候是合法的!
3. 所以，mysql中，一般而言，数据类型本身也是一种: 约束

🏳️‍🌈2.2 bit 类型

我们先创建数据表 t2 用来表示一个用户是否在线

mysql> create table ifnot exists t2(-> id int,-> online bit(8)->)^C
mysql> create table ifnot exists t2(-> id int,-> online bit(1)-> ）；
    ->^C
mysql> create table ifnot exists t2(-> id int,-> online bit(1)->);
Query OK,0 rows affected(0.02 sec)

mysql> desc t2;+--------+---------+------+-----+---------+-------+| Field  | Type    | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| id     |int(11)| YES  ||NULL||| online |bit(1)| YES  ||NULL||+--------+---------+------+-----+---------+-------+2 rows in set(0.00 sec)

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

插入数据

mysql> insert into t2(id, online)values(123,0);
Query OK,1 row affected(0.00 sec)

mysql> insert into t2(id, online)values(123,1);
Query OK,1 row affected(0.00 sec)

mysql> insert into t2(id, online)values(123,3);
ERROR 1406(22001): Data too longfor column 'online' at row 1
mysql> insert into t2(id, online)values(123,5);
ERROR 1406(22001): Data too longfor column 'online' at row 1
mysql> insert into t2(id, online)values(123,2);
ERROR 1406(22001): Data too longfor column 'online' at row 1

我们发现插入 0 或者 1 的 bit 值的时候都成功了，但是插入非01值时都失败了

mysql> select * from t2;+------+--------+| id   | online |+------+--------+|123|||123||+------+--------+2 rows in set(0.00 sec)

mysql> select id,hex(online) from t2;+------+-------------+| id   |hex(online)|+------+-------------+|123|0||123|1|+------+-------------+2 rows in set(0.00 sec)

我们打印表的内容直接打印是打印不出来的，转换为十六进制表示形式后才能输出

这是因为bit类型在存储时是以ASCLL码的形式存储的

我们将bit的值改成10看看，能否再插入3，5，2等数字

mysql> alter table t2 modify online bit(10);
Query OK,2 rows affected(0.08 sec)
Records:2  Duplicates:0  Warnings:0

mysql> desc t2;+--------+---------+------+-----+---------+-------+| Field  | Type    | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| id     |int(11)| YES  ||NULL||| online |bit(10)| YES  ||NULL||+--------+---------+------+-----+---------+-------+2 rows in set(0.00 sec)

mysql> insert into t2(id, online)values(123,3);
Query OK,1 row affected(0.01 sec)

mysql> insert into t2(id, online)values(123,5);
Query OK,1 row affected(0.01 sec)

mysql> insert into t2(id, online)values(123,2);
Query OK,1 row affected(0.00 sec)

mysql> select id,hex(online) from t2;+------+-------------+| id   |hex(online)|+------+-------------+|123|0||123|1||123|3||123|5||123|2|+------+-------------+5 rows in set(0.00 sec)

就能插进来了

🏳️‍🌈1.3 float 类型

先创建一个数据表 t3

// float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入
mysql> create table ifnot exists t3(-> id int,-> salary float(4,2)->);
Query OK,0 rows affected(0.02 sec)

mysql> show tables;+-------------------+| Tables_in_test_db |+-------------------+| t1                || t2                || t3                |+-------------------+3 rows in set(0.00 sec)

mysql> desc t3
    ->;+--------+------------+------+-----+---------+-------+| Field  | Type       | Null | Key | Default | Extra |+--------+------------+------+-----+---------+-------+| id     |int(11)| YES  ||NULL||| salary |float(4,2)| YES  ||NULL||+--------+------------+------+-----+---------+-------+2 rows in set(0.00 sec)

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

mysql> insert into t3(id, salary)values(1,99.99);
Query OK,1 row affected(0.01 sec)

mysql> insert into t3(id, salary)values(2,-99.99);
Query OK,1 row affected(0.01 sec)

mysql> insert into t3(id, salary)values(3,100.00);
ERROR 1264(22003): Out of range value for column 'salary' at row 1
mysql> select * from t3
    ->;+------+--------+| id   | salary |+------+--------+|1|99.99||2|-99.99|+------+--------+2 rows in set(0.00 sec)

🏳️‍🌈1.4 decimal 类型

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

mysql> create table ifnot exists t4(-> f1 float(10,8),-> f2 decimal(4,2)->);
Query OK,0 rows affected(0.02 sec)

mysql> desc t4;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| f1    |float(10,8)| YES  ||NULL||| f2    |decimal(4,2)| YES  ||NULL||+-------+--------------+------+-----+---------+-------+2 rows in set(0.01 sec)

mysql> insert into t4(f1, f2)values(10.0,99.99);
Query OK,1 row affected(0.01 sec)

mysql> insert into t4(f1, f2)values(10.0,-99.99);
Query OK,1 row affected(0.00 sec)

mysql> insert into t4(f1, f2)values(10.0,-999.99);
ERROR 1264(22003): Out of range value for column 'f2' at row 1
mysql> insert into t4(f1, f2)values(10.0,999.99);
ERROR 1264(22003): Out of range value for column 'f2' at row 1
mysql> insert into t4(f1, f2)values(10.0,99.999);
ERROR 1264(22003): Out of range value for column 'f2' at row 1
mysql> insert into t4(f1, f2)values(10.0,99.994);
Query OK,1 row affected,1warning(0.01 sec)

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

mysql> select * from t4;+-------------+--------+| f1          | f2     |+-------------+--------+|10.00000000|99.99||10.00000000|-99.99||10.00000000|99.99|+-------------+--------+3 rows in set(0.00 sec)

那既然这两个类型很像，decimal 类型的作用是什么

我们将两个类型的范围改成一样

mysql> alter table t4 modify f2 decimal(10,8);
Query OK,3 rows affected(0.05 sec)
Records:3  Duplicates:0  Warnings:0
mysql> insert into t4(f1, f2)values(23.12345612,23.12345612);
Query OK,1 row affected(0.00 sec)

mysql> select * from t4;+-------------+--------------+| f1          | f2           |+-------------+--------------+|10.00000000|99.99000000||10.00000000|-99.99000000||10.00000000|99.99000000||23.12345695|23.12345612|+-------------+--------------+4 rows in set(0.00 sec)

我们发现 float 类型存的和原始的数据有一点的精度差距，但 decimal 类型保证了精度

float表示的精度大约是7位

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

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

🏳️‍🌈1.5 char 类型

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

mysql>  create table ifnot exists t5(-> id int,-> name char(2)->);
Query OK,0 rows affected(0.03 sec)

mysql> desc t5
    ->;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    |int(11)| YES  ||NULL||| name  |char(2)| YES  ||NULL||+-------+---------+------+-----+---------+-------+2 rows in set(0.00 sec)

mysql> insert into t5(id, name)values(1,'a');
Query OK,1 row affected(0.01 sec)

mysql> insert into t5(id, name)values(1,'b');
Query OK,1 row affected(0.00 sec)

mysql> insert into t5(id, name)values(1,'ab');
Query OK,1 row affected(0.01 sec)

mysql> insert into t5(id, name)values(1,'abc');
ERROR 1406(22001): Data too longfor column 'name' at row 1
mysql> select * from t5;+------+------+| id   | name |+------+------+|1| a    ||1| b    ||1| ab   |+------+------+3 rows in set(0.00 sec)

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

🏳️‍🌈1.6 varchar 类型

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

使用方法和char一模一样

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

1. varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字 节数是65532。
2. 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占 用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符 占用2字节）

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

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

👥总结

本篇博文对 ? 做了一个较为详细的介绍，不知道对你有没有帮助呢

觉得博主写得还不错的三连支持下吧！会继续努力的~