mysql07-数据类型
MySQL支持多种数据类型主要有：
- 数值数据类型:
- 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
- 浮点小数数据类型：FLOAT、DOUBLE。
- 定点小数类型：DECIMAL。
- 日期/时间类型：YEAR、TIME、DATE、DATETIME和TIMESTAMP。
- 字符串类型:
- 文本字符串：CHAR、VARCHAR、TEXT、ENUM、SET。
- 二进制字符串：BINARY、VARBINARY、BLOB。
1、整数类型
- MySQL中的整数型数据类型占用的字节数
- MySQL中不同整数类型的取值范围
- 显示宽度和数据类型的取值范围是无关的。
- 显示宽度是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。
- 如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够完全显示出来。
- 在定义表结构时可以指定整型数据类型的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
- 显示宽度只用于显示，并不能限制取值范围和占用空间，如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999，而是INT整型所允许的最大值。
示例：
create table tb_test1(
n0 int(3),
n1 tinyint,
n2 smallint,
n3 int,
n4 bigint
);
mysql> desc tb_test1; --注意，n0和n3的区别
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| n0 | int(3) | YES | | NULL | |
| n1 | tinyint(4) | YES | | NULL | |
| n2 | smallint(6) | YES | | NULL | |
| n3 | int(11) | YES | | NULL | |
| n4 | bigint(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from tb_test1; --注意n0显示的值的位数
+--------+------+------+------+------+
| n0 | n1 | n2 | n3 | n4 |
+--------+------+------+------+------+
| 444444 | 127 | NULL | NULL | NULL |
+--------+------+------+------+------+
2、浮点数类型和定点数类型
- MySQL中的小数类型占用的字节数
- MySQL中不同小数类型的取值范围
- 浮点类型有两种：
- 单精度浮点类型(FLOAT)
- 双精度浮点类型(DOUBLE)
- 定点类型只有一种：DECIMAL
- 浮点类型和定点类型都可以用(M, N)来表示，其中M称为精度，表示总共的位数；N称为标度，是表示小数的位数。
- FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度(由计算机硬件和操作系统决定)，DECIMAL如不指定精度，默认为(10,0)。
- 不论是定点类型还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。
- DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以字符串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。在对精度要求比较高的时候(如货币，科学数·据等)使用DECIMAL的类型比较好。
- 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围:它的缺点是会引起精度问题。
- 两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时需要注意，并尽量避免做浮点数比较。
示例：
--创建表
create table tb_test2(
n1 float(3, 1),
n2 double(3, 1),
n3 decimal(3, 1),
n4 float,
n5 double,
n6 decimal
);
mysql> desc tb_test2; --查看表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| n1 | float(3,1) | YES | | NULL | |
| n2 | double(3,1) | YES | | NULL | |
| n3 | decimal(3,1) | YES | | NULL | |
| n4 | float | YES | | NULL | |
| n5 | double | YES | | NULL | |
| n6 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
mysql> insert into tb_test2(n1,n2,n3,n4,n5,n6) values(11.12,11.23,11.34,11.45,11.56,11.67); --插入数据
mysql> select * from tb_test2; --查看表数据
+------+------+------+-------+-------+------+
| n1 | n2 | n3 | n4 | n5 | n6 |
+------+------+------+-------+-------+------+
| 11.1 | 11.2 | 11.3 | 11.45 | 11.56 | 12 |
+------+------+------+-------+-------+------+
3、日期与时间类型
- 每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。
- 日期与时间数据类型
1、YEAR
指定YEAR值的方式：
- 以4位字符串或者4位数字格式表示的YEAR，范围为'1901'~'2155'。输入格式为'YYYY'，或者YYYY。
- 以2位字符串格式表示的YEAR，范围为'00'~'99'。
- '00'~'69':被转换为2000~2069。'0'与'00'的作用相同。
- '70'~'99':被转换为1970~1999。
- 以2位数字表示的YEAR，范围为1~99。
- 1~69：被转换为2001~2069。
- 70~99：被转换为1970~1999。
- 注意：在这里0值将被转换为0000，而不是2000。
示例：
--创建表
create table tb_year(
yyyy year
);
mysql> insert into tb_year(yyyy) values(1901),(2155);
mysql> insert into tb_year(yyyy) values('1901'),('2155');
2、TIME
- TIME类型用于只需要时间信息的值，在存储时需要3个字节。
- TIME类型的格式为'HH:MM:SS'。HH表示小时，MM表示分钟，SS表示秒。
- TIME类型的取值范围为-838:59:59 ~838:59:59，小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时) ,还可能是某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负) 。
指定TIME值的方式：
- 'D HH:MM:SS'格式的字符串。还可以使用下面任何一种“非严格”的语法： 'D HH:MM'、"D HH'、'HH:MM:SS'、'HH:MM'或'ss'。这里的D表示日，可以取0-34之间的值。在插入数据库时，D被转换为小时保存，格式为"D*24+HH"。
- 使用冒号或天数，必须是字符串格式。
- 在使用'D HH'格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。
- 'HHMMSS'格式的没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如:'101112'被理解为'10:11:12'。
- TIME值中如果没有冒号，MySQL解释值时，最右边的两位表示秒（被看作过去的时间）。例如：'1112'和1112，MySQL将它们解释为00:11:12(即11分12秒)；'12'和12被解释为00:00:12。
- TIME值中如果有冒号，MySQL解释值时，最左边的两位表示小时（被看作当天的时间）。例如：'11:12'，MySQL将它解释为11:12:00。
示例：
--创建表
create table tb_time(
t1 time,
t2 time
);
--插入数据
mysql> insert into tb_time(t1,t2) values('10 1:10:10','10 01'),(101010,'101010'),(1010,'10:10');
mysql> insert into tb_time(t1,t2) values(CURRENT_TIME,NOW()); --插入当前系统时间
mysql> select * from tb_time; --查看表数据
+-----------+-----------+
| t1 | t2 |
+-----------+-----------+
| 241:10:10 | 241:00:00 |
| 10:10:10 | 10:10:10 |
| 00:10:10 | 10:10:00 |
| 15:02:13 | 15:02:13 |
+-----------+-----------+
3、DATE
- DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3个字节。
- DATE类型格式为"YYYY-MM-DD'其中YYYY表示年，MM表示月，DD表示日。
指定DATE值的方式：
- 以'YYYY-MM-DD'或者'YYYYMMDD'字符串格式表示的日期，取值范围为'1000-01-01'~'9999-12-3'。
- 例如，输入'2012-12-31'或者'20121231'， 插入数据库的日期都为2012-12-31。
- 以'YY-MM-DD'或者'YYMMDD'字符串格式表示的日期，在这里YY表示两位的年值。
- MySQL使用以下规则解释两位年值: '00-69'范围的年值转换为'2000~2069'，'70~99'，范围的年值转换为'1970~1999' 。
- 例如，输入'12-12-31'插入数据库的日期为2012-12-31，输入'981231'插入数据的日期为1998-12-31。
- 以YY-MM-DD或者YYMMDD数字格式表示的日期。
- MySQL使用以下规则解释两位年值：00-69范围的年值转换为2000~2069，70~99范围的年值转换为1970~1999。
- 例如，输入12-12-31插入数据库的日期为2012-12-31;输入981231，插入数据的日期为1998-12-31。
- 使用CURRENT_DATE或者NOWO，插入当前系统日期。
- CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，但在保存到数据库时，只保留了其日期部分。
示例：
--创建表
create table tb_date(
d1 date,
d2 date
);
mysql> insert into tb_date(d1, d2) values('2021-08-15','20210815');
mysql> insert into tb_date(d1, d2) values(CURRENT_DATE,NOW()); --插入当前系统日期
mysql> select * from tb_date; --查看表数据
+------------+------------+
| d1 | d2 |
+------------+------------+
| 2021-08-15 | 2021-08-15 |
| 2021-08-15 | 2021-08-15 |
+------------+------------+
4、DATETIME
- DATETIME类型用在需要同时包含日期和时间信息的值，在存储时需要8个字节。
- DATETIME类型的格式为'YYYY-MM-DD HH:MM:SS'。其中YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。
指定DATETIME值的方式：
- 以'YYYY-MM-DD HH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示的值，取值范围为'1000-01-01 00:00:00'~'9999-12-3 23:59:59'。
- 例如：'2012-12-31 05:05:05'或者'20121231050505'插入数据库的DATETIME值都为2012-12-31 05:05:05
- 以'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示的日期，在这里YY表示两位的年值。
- MySQL使用以下规则解释两位年值：00-69范围的年值转换为2000~2069，70~99范围的年值转换为1970~1999。
- 例如：'12-12-31 05:05:05'插入数据库的DATETIME为2012-12-31 05:05:05，'980505050505'插入数据库的DATETIME为1998-05-05 05:05:05.
- 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。
- 例如：20121231050505插入数据库的DATETIME为2012-12-31 05:05:05，981231050505插入数据的DATETIME为1998-12-31 05:05:05。
示例：
--创建数据表 create table tb_datetime( dt1 datetime, dt2 datetime ); insert into tb_datetime(dt1) values(now()); --插入当前系统日期和时间 mysql> select * from tb_datetime; --查看表数据 +---------------------+------+ | dt1 | dt2 | +---------------------+------+ | 2021-08-15 15:48:13 | NULL | +---------------------+------+
5、TIMESTAMP
- TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS，在存储时需要4个字节。
- TIMESTAMP取值范围小于DATETIME，为'1970-01-01 00:00:01' UTC~"2038-01-19 03:14:07' UTC。
- UTC(Coordinated Universal Time)为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。
- TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是:
- DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关;
- 而TIMESTAMP值的存储是以UTC (世界标准时间)格式保存的，在储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
示例：
--创建表 create table tb_timestamp( ts timestamp ); mysql> insert into tb_timestamp(ts) values(now()); --插入当前系统日期和时间 mysql> select * from tb_timestamp; --产看表数据 +---------------------+ | ts | +---------------------+ | 2021-08-15 15:59:37 | +---------------------+
4、文本字符串类型
- MySQL支持两类字符型数据：文本字符串和二进制字符串。
- MySQL文本字符串数据类型
- VARCHAR和TEXT类型是变长类型，其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度L，加上1个字节以记录字符串的长度。对于字符"abcd"，L是4而存储要求是5个字节。
1、CHAR和VARCHAR
- CHAR(M)为固定长度字符串，在定义时指定字符串列长。
- M表示列长度，M的范围是0~255个字符。
- 当保存时在右侧填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格将被删除掉。
- 例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。
- VARCHAR(M)是长度可变的字符串，M表示最大列长度。
- M表示列的最大长度，M的范围是0~65535。
- VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。
- VARCHAR在值保存和检索时尾部的空格仍保留。
- 例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。
- CHAR(4)与VARCHAR(4)存储区别
示例：
--创建表
create table tb_char(
c char(4),
vc varchar(4)
);
mysql> insert into tb_char(c,vc) values('',''),('a','a'),('a ','a '),('abcd','abcd'); --插入数据
mysql> select concat('(',c,')'),concat('(',vc,')') from tb_char; --查看数据表
+-------------------+--------------------+
| concat('(',c,')') | concat('(',vc,')') |
+-------------------+--------------------+
| () | () |
| (a) | (a) |
| (a) | (a ) | --注意，char后面的空格没有保留
| (abcd) | (abcd) |
+-------------------+--------------------+
2、TEXT
- TEXT列保存非二进制字符串，如文章内容、评论等。
- 当保存或查询TEXT列的值时，不删除尾部空格。
- Text类型分为4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
- 不同的TEXT类型的存储空间和数据长度不同。
- TINYTEXT最大长度为255(28-1)字符的TEXT列。
- TEXT最大长度为65535(216-1)字符的TEXT列。
- MEDIUMTEXT最大长度为16777215(224-1)字符的TEXT列。
- LONGTEXT最大长度为4294967295或4GB(232-1)字符的TEXT列。
3、ENUM
- ENUM是一个字符串对象，其值是表创建时规定的一列值。
- 枚举最多可以有65535个元素。
- 当创建表时，ENUM元素值的尾部的空格将自动被删除。
- ENUM类型的字段在取值时只能在指定的枚举列表中取，而且一次只能取一个。
- ENUM列总有一个默认值。
- 如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL。
- 如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。
- ENUM值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。
- 基本语法格式如下：
字段名 ENUM('值1', '值2', ..., '值n')
示例：
--创建表
create table tb_enum(
em enum('first','second','third')
);
mysql> insert into tb_enum(em) values('first'),('second'),('third'),(null); --插入数据
mysql> select em,em+0 from tb_enum; --查看表数据，em+0列是枚举列的索引值（字段名 加 零）
+--------+------+
| em | em+0 |
+--------+------+
| first | 1 |
| second | 2 |
| third | 3 |
| NULL | NULL |
+--------+------+
mysql> insert into tb_enum(em) values(1),(2),(3),(null); --和上面插入数据的效果相同
4、SET
- SET是一个字符串对象，其值为表创建时规定的一列值
- SET列最多可以有64个元素。
- 当创建表时，SET元素值的尾部空格将自动被删除。
- SET类型的字段在取值时只能在指定的集合列表中取，一次可以取零个或多个值。
- 与ENUM类型相同的是，SET值在内部用整数表示，列表中每一个值都有一个索引编号。
- 与ENUM类型不同的是：
- ENUM类型的字段只能从定义的列值中选择一个值插入。
- SET类型的字段可从定义的列值中选择零个或多个值插入。
- 如果插入SET字段中列值有重复，则MySQL自动删除重复的值。
- 插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示。
- 如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。
- 基本语法格式如下：
SET('值1', '值2', ..., '值n')
示例：
--创建表
create table tb_set(
st set('a','b','c','d')
);
mysql> insert into tb_set(st) values('a'),('a,b,c,d'),(''),('a,c,a'),('d,a,c'),('a'); --插入数据
mysql> select * from tb_set; --查看表数据
+---------+
| st |
+---------+
| a |
| a,b,c,d |
| | --注意，空字符串
| a,c | --注意，插入的值是'a,c,a'。 --重复值
| a,c,d | --注意，插入的值是'd,a,c'。 --顺序
| a |
+---------+
5、二进制字符串类型
- MySQL中的二进制字符串类型
1、BIT
- BIT类型是位字段类型。M表示每个值的位数，范围为1-64，如果M被省略，默认为1。
- 如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配个值b'101'，其效果与分配b'000101'相同。
- BIT数据类型用来保存位字段值。例如，以二进制的形式保存数据13，13的二进制形式为1101，在这里需要位数至少为4位的BIT类型。
- 默认情况下，MySQL不可以插入超出该列允许范围的值，因而插入的数据要确保插入的值在指定的范围内。
示例：
- b+0表示将二进制的结果转换为对应的数字的值,BIN()函数将数字转换为二进制。
--创建表 create table tb_bit( b bit(7) ); mysql> insert into tb_bit(b) values(0),(127); --插入数据 mysql> select * from tb_bit; +------+ | b | +------+ | | | | +------+ mysql> select b+0 from tb_bit; +------+ | b+0 | +------+ | 0 | | 127 | +------+ mysql> select bin(b+0) from tb_bit; +----------+ | bin(b+0) | +----------+ | 0 | | 1111111 | +----------+
2、BINARY和VARBINARY
- BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充'\0'，以达到指定长度。
- VARBINARY类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间。实际占用的空间为字符串的实际长度加1。
- 基本语法格式如下：
字段名 BINARY(M) 字段名 VARBINARY(M)
示例：
--创建表
create table tb_bin(
biny binary(5),
varbiny varbinary(5)
);
mysql> insert into tb_bin(biny,varbiny) values('a','a'),('abcde','abcde'),('a ','a '); --插入数据
mysql> select concat('(',biny,')'),length(biny),concat('(',varbiny,')'),length(varbiny) from tb_bin; --查看数据
+----------------------+--------------+-------------------------+-----------------+
| concat('(',biny,')') | length(biny) | concat('(',varbiny,')') | length(varbiny) |
+----------------------+--------------+-------------------------+-----------------+
| (a ) | 5 | (a) | 1 |
| (abcde) | 5 | (abcde) | 5 |
| (a ) | 5 | (a ) | 4 |
+----------------------+--------------+-------------------------+-----------------+
3、BLOB
- BLOB是一个二进制大对象，用来存储可变数量的数据。
- BLOB类型的存储范围
- BLOB列存储的是二进制字符串(字节字符串)；TEXT列存储的是非二进制字符串(字符字符串)。
- BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。