MySQL数据类型

南桥

发布于 2024-07-26 12:52:47

250

发布于 2024-07-26 12:52:47

文章被收录于专栏：南桥谈编程

数据类型分类

简单看一下数据可常见类型：

数值类型

tinyint类型

创建一个表，表中内容是tinyint型有符号：

mysql> create table if not exists t1 (
    -> num tinyint
    -> );
Query OK, 0 rows affected (0.16 sec)

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

tinyint数据越界测试：

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

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

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

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

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

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

mysql> insert into t1 values (129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values (-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

由此可见，tinyint是有符号的范围是-128 ~ 127。

创建一个表，表中内容是tinyint unsigned型无符号：

mysql> create table if not exists t2(
    -> num tinyint unsigned
    -> );
Query OK, 0 rows affected (0.03 sec)
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| num   | tinyint unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

tinyint unsigned数据越界测试：

mysql> insert into t2 values (0);
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into t2 values (-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (255);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

由此可见，tinyint unsigned是有符号的范围是0 ~ 255。

总结：如果插入的数据超过MySQL数据类型的范围，那么MySQL就不让你插入，直接拦截，不让做对应的操作。这点和C/C++一样，但是编译器不会报错，语言上给你发生截断，编译器内部可能会隐式转换。如果已经有数据被成功插入到MySQL中，那么插入的时候一定是合法的。因此，MySQL中，一般而言，数据类型本身也是一种约束。这种约束倒逼程序员，让程序员尽可能正确插入，如果不正确插入，MySQL也能保证插入的数据合法性。这样就能保证数据库中的数据是可预期完整的。

在MySQL表中建立属性列：列名称类型例如：num tinyint unsigned

注意： 尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型

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

mysql> create table if not exists t3( id int, online bit(1) );
Query OK, 0 rows affected (0.03 sec)

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

做插入测试：

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

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

mysql> insert into t3 (id,online) values (124,3);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t3 (id,online) values (124,2);
ERROR 1406 (22001): Data too long for column 'online' at row 1

因此，bit为只有一位。

查看刚刚插入的位结构：

mysql> select * from t3;
+------+----------------+
| id   | online         |
+------+----------------+
|  123 | 0x00           |
|  124 | 0x01           |
+------+----------------+
2 rows in set (0.00 sec)

将bit位改成10，即bit(10)：

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

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

此时再去插入3,5，10等就可以插入，因为bit位数变多了。

设置位图结构时，最多只能是64位：

mysql> create table if not exists t4(
    -> id int,
    -> online bit(65)
    -> );
ERROR 1439 (42000): Display width out of range for column 'online' (max = 64)

后面的括号如果什么都不带，默认建立的是位数为1的位图结构：

mysql> create table if not exists t4( id int, online bit );
Query OK, 0 rows affected (0.03 sec)

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

浮点类型

float

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

样例：小数：float(4,2)表示允许存装数据的位数是4，精度部分是3个。

mysql> create table if not exists t5(
    -> id int,
    -> salary float(4,2)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

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

插入数据：

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

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


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

mysql> select * from t5;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    1 | -99.99 |
|    1 |  12.30 |
+------+--------+
3 rows in set (0.00 sec)

在进行浮点数存储时，要求2位精度，但是你存入更高的精度，那么就会四舍五入：

建立一个无符号类型的浮点数：

mysql> create table if not exists t6(
    -> id bigint,
    -> salary float(4,2) unsigned
    -> );
Query OK, 0 rows affected, 2 warnings (0.08 sec)

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

插入数据测试：

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

mysql> insert into t6 (id,salary) values(1,-99.99);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 (id,salary) values(1,-1);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

decimal

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

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

mysql> create table if not exists t7( f1 float(10,8), f2 decimal(4,2) );
Query OK, 0 rows affected, 1 warning (0.05 sec)

它的使用和float一模一样，但是它的精度更准确一些。

字符串类型

char

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

样例：

mysql> create table if not exists t8(
    -> id int,
    -> name char(2)
    -> );
Query OK, 0 rows affected (0.02 sec)

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

插入的数据超出固定长度则无法插入：

可以插入汉字：

varchar

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

样例：创建一个表：

mysql> create table if not exists t9(
    -> id int,
    -> name varchar(6)
    -> );
Query OK, 0 rows affected (0.02 sec)

插入数据，超出6，则无法插入：

说明：关于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字节）。

char和varchar对比

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

如果数据确定长度都一样，就使用定长（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 完全一致，占用四字节

样例：创建表：

mysql> create table if not exists t10(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp
    -> );
Query OK, 0 rows affected (0.04 sec)

插入数据：

mysql> insert into t10 (t1, t2) values('2024-7-23','2024-7-23 17:14:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t10;
+------------+---------------------+------+
| t1         | t2                  | t3   |
+------------+---------------------+------+
| 2024-07-23 | 2024-07-23 17:14:00 | NULL |
+------------+---------------------+------+
1 row in set (0.00 sec)

enmu和set类型

语法： enum：枚举，“单选”类型； enum('选项1','选项2','选项3',...); 该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型； set('选项值1','选项值2','选项值3', ...); 该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…最多64个

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

mysql> create table if not exists votes(
    -> username varchar(30),
    -> gender enum('男','女'),
    -> hobby set('代码','羽毛球','乒乓球','篮球','游泳')
    -> );
Query OK, 0 rows affected (0.03 sec)

插入的时候，性别列只能插入‘男’或者‘女’或者插入相应常量的下标，下表是按照枚举的顺序来的。

mysql> insert into votes values('貂蝉','女','代码');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('关羽','男','代码');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('关羽',1,'代码');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('关羽',2,'代码');
Query OK, 1 row affected (0.01 sec)

mysql> select * from votes;
+----------+--------+--------+
| username | gender | hobby  |
+----------+--------+--------+
| 张飞     | 男     | 代码   |
| 貂蝉     | 女     | 代码   |
| 关羽     | 男     | 代码   |
| 关羽     | 男     | 代码   |
| 关羽     | 女     | 代码   |
+----------+--------+--------+
5 rows in set (0.00 sec)

如果是集合类型，不可以插入集合中不存在的元素，可以以逗号为分隔符，插入好几个。采用比特位位置来个set中的爱好对应起来

mysql> insert into votes values('曹操',1,'乒乓球,篮球,代码');
Query OK, 1 row affected (0.01 sec)


在enmu中筛选出男：

```bash
select * from votes where gender='男';

在集合中筛选出喜欢羽毛球的：

select * from votes where hobby='羽毛球';

这里问题来了，要筛选的是筛选出喜欢羽毛球的，不是筛选出只喜欢羽毛球的，于是就有了集合查询。

MySQL筛选函数

集合查询使用find_ in_ set函数： find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。

a在a,b,c中，d不在a,b,c中：

mysql> select find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('d','a,b,c');
+--------------------------+
| find_in_set('d','a,b,c') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

查询喜欢打羽毛球的人：