MySQL数据类型

文章目录

• • 1. 数据类型分类
  • 2. 数值类型
  • • 2.1 整数类型
    • 2.2 bit类型
    • 2.3 浮点类型
  • 3. 字符串类型
  • • 3.1 char
    • 3.2 varchar
    • 3.3 日期、时间类型
    • 3.4 enum和set

1. 数据类型分类

2. 数值类型

2.1 整数类型

默认都是有符号整数

如果想要是无符号，可以在定义的时候在后面带上unsigned

tinyint类型举例：

建一个tinyint数据类型的表（默认有符号）：

mysql> create database test_db;
mysql> use test_db;
mysql> create table if not exists test1_tinyint(
    -> num tinyint 
    -> );

插入符合数据范围的值：

mysql> insert into test1_tinyint values (-128);
mysql> insert into test1_tinyint values (-127);
mysql> insert into test1_tinyint values (0);

插入超出范围的值：

这里直接报错拦截

建一个tinyint数据类型的表（无符号）：

mysql> create table if not exists test1_tinyint_unsigned(
    -> num tinyint unsigned
	-> );

插入数据：

在C/C++当中，如果数据超出了类型的范围，编译器可能不会报错，而是直接截断；

在mysql特定类型中插入不合法的数据，mysql会直接拦截；

反之，然后插入成功则表明数据一定合法，所以在mysql当中，数据类型本身也是一种约束。

这就会倒闭使用者进行正确的数据插入，无论使用是否熟练，mysql都能保证数据合法性。

对于其他整型SMALLINT、MEDIUMINT、INT、BIGINT也是一样

Tips：

尽量不使用unsigned，对应int（或其他整型）类型存不下的数据，int unsigned同样可能存不下，还不如将其类型提升

当然，有些类型就得是无符号的，例如年龄、日期，具体结合实际

2.2 bit类型

bit表示比特位类型，默认只有1个比特位，范围是1~64

创建含有bit类型数据的表：

mysql> create table if not exists test2_bit(
    -> id int,
    -> online bit(1)
    -> );

插入数据：

mysql> insert into test2_bit (id, online) values (9,1);
mysql> insert into test2_bit (id, online) values (27,1);
mysql> insert into test2_bit (id, online) values (24,0);
mysql> insert into test2_bit (id, online) values (24,2);	#error
mysql> insert into test2_bit (id, online) values (24,3);	#error

查看：

这里是以ASCII码值显示的

修改比特位数：

mysql> alter table test2_bit modify online bit(10);

bit类型括号内容变为10，表示10个比特位

此时插入字符：

mysql> insert into test2_bit (id, online) values (15, 'a');
mysql> insert into test2_bit (id, online) values (15, 98);

2.3 浮点类型

float类型语法示例：

float[(m, d)] [unsigned]	# m指定显示长度, d指定小数长度位数, float总共占用4个字节

mysql> create table if not exists test3_float(
    -> id int,
    -> score float(4,2)
    -> );

插入数据：

mysql> insert into test3_float (id, score) values (1, 88.5);
mysql> insert into test3_float (id, score) values (2, 96.32);
mysql> insert into test3_float (id, score) values (3, 92);
mysql> insert into test3_float (id, score) values (4, -4.21);

当插入数据的小数部分位数超出之后，mysql会自动四舍五入；但如果进位导致整数部分位数超出，会拦截报错

对于浮点数类型，如果采用无符号，mysql会直接将负数部分砍掉：

decimal类型语法：

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

decimal的用法和float一样，但是decimal的精度更高

mysql> create table if not exists test4_decimal(
    -> f1 float(10,8),
    -> f2 decimal(10,8)
    -> );

插入数据发现float类型在精度过大的时候，会做一些处理，而decimal不会

float类型的精度大约是7位

3. 字符串类型

3.1 char

char类型是固定长度字符串

char(L)	#L表示可存储的长度, 单位是字符, 最大长度为255

mysql> create table if not exists test5_char( id int, name char(2));

mysql> insert into test5_char (id, name) values (1,'a');
mysql> insert into test5_char (id, name) values (1,'ab');
mysql> insert into test5_char (id, name) values (1,'实');
mysql> insert into test5_char (id, name) values (1,'实习');
mysql> insert into test5_char (id, name) values (1,'abc');
mysql> insert into test5_char (id, name) values (1,'实习吗');

在utf8编码当中一个汉字占3个字节；gbk编码一个汉字占2个字节。

但是这里可以直接插入，这是因为mysql当中的字符和C/C++当中的字符概念是不一样的，mysql当中的字符就是一种符号。

一个汉字是一个字符、一个字母也是一个字符。

3.2 varchar

varchar叫做边长字符串，语法：

varchar(L)	# 可变长字符串, L表示字符串长度, 最长为65535个字节

mysql> create table if not exists test6_varchar(
    -> id int,
    -> name varchar(6)
    -> );

这里插入数据，发现数据超过对应的大小时，也会直接拦截

将长度设置为最大值65535：

这里报错显示最大值为21845，这是因为varchar最大为65535个字节，utf8编码单字符3字节，所以这个21845 = 65535/3，表示的是存储最大的字符数。

char为固定长度字符串，就和C/C++当中定义的数组，如果申请的是6个字符大小，就是6*3 = 18 byte，就算使用的时候只用了一个字符空间，照样给6个。

varchar(L)这里的L表示最大上限，如果申请的是6个字符大小，只用了一个，varchar只会给一个。

对varchar(L)的L到底能多大，这和表的编码有关：

• varchar长度范围是[0, 65535]，可是要有1~3个字节用户记录数据大小，所以有效字节数是65532。
• 采用utf8编码时，varchar(L)的L最大值是65532/3 = 21844；
  采用gbk编码时，varchar(L)的参数L最大是65532/2=32766。

3.3 日期、时间类型

常用日期类型：

• date：日期yyyy-mm--dd，占3字节
• datetime：时间日期格式，yyyy-mm-dd HH:ii:ss范围从1000到9999，占8字节
• timestamp：时间戳，表示从1970年到现在累计的秒数，格式和datetime一样，占4字节

mysql> create table if not exists test8(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp
    -> );

时间戳这个类型有个拓展说明，自动更新时间戳，对该表进行操作时，会自动更新时间戳

timestamp实际应用场景：

发布文章的时候，会记录时间，如果修改这个文章，时间也会跟着改变；

datetime实际应用场景：

有人犯错了，去局子里谈话，这里就要很严谨记录开始时间；

date实际应用场景：

对象的生日

3.4 enum和set

enum是枚举类型，set是集合类型

• enum(opt1, opt2, opt3)，这些选项是单选
• set(opt1, opt2, opt3)，这些选项是多选

建立含enum和set类型的表：

mysql> create table if not exists votes (
    -> username varchar(30),
    -> gender enum('男', '女'),
    -> hobby set('学习', '骑车', '做饭', '唱歌')
    -> );

enum可以采用直接指定或者下标插入，而set这里用“数字”插入，并不是和enum一样采用的下标，set采用的是比特位标记的方式

不建议用数字插入，因为这样不利于阅读！

enum和set可以默认为空

这里的null和 ，null什么也没有，而 空表示有，但是是空串

在enum中查找：

mysql> select * from votes where gender='男';	#筛选全部男生
mysql> select * from votes where gender='女';	#筛选全部女生

在set中查找：

mysql> select * from votes where hobby='骑车';

这里是严格筛选兴趣只用骑车的人，但是有的人兴趣有多个，里面包含了骑车

find_in_set函数查找一个元素是否在对应的集合当中，返回0表示假，为真则返回下标

mysql> select * from votes where find_in_set('骑车', hobby);

and表示&，or表示|