阅读 106

2-MySQL的数据类型

前言

MySQL的数据类型分为五大类: 数值类型、字符串类型、时间类型、空间类型和json类型(5.7新增)。每个大类下面又有很多具体类型,它们的不同主要体现在长度范围、精度和需要的物理空间(磁盘和内存)。

注: 本文讲述基于MySQL5.7, 有些内容5.7 版本以下的可能不适用,如json类型

数值类型

数值类型只是个大类,它下面还有BIT、INT、FLOAT、DECIMAL等具体分类。

MySQL对数值类型的定义有如下约定:
1.符号[]表示里面的参数是可选的

2.对于整数类型,M表示该类型显示的最大长度,与实际存储的范围无关;对于实数类型(Float、Double、Decimal),M表示该类型存储的最大长度

3.对于整数类型,有SIGNED和UNSIGNED两种属性,默认属性为SIGNED,能够存储负数。如果属性为UNSIGNED,则不允许存储负数。如果没有存储负数的需求,UNSIGNED属性的类型可以使存储的范围提高一倍。比如TINYINT的存储范围是 -128-127,而UNSIGNED TINYINT 的存储范围是0-255。

4.如果定义一个类型时指定了ZEROFILL属性,则该类型默认带有UNSIGNED属性,即不能存储负数

5.“SERIAL” 类型是 “BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE” 的别名。

6.“serial default value“ 是 ”NOT NULL AUTO_INCREMENT UNIQUE” 的别名

# 实验1
create table mysql_test_serial (
    id int(11) not null primary key,
    single_serial serial -- 由于主键id没有指定自增,所以允许添加
);

create table mysql_test_serial2 (
    id int(11) not null primary key,
    serial_attribute int(11) serial default value,  -- 由于主键id没有指定自增,所以允许添加
    zerofill_attribute int(11) zerofill not null default 0, -- 测试是否默认带有unsigned属性
    unsigned_attribute int(11) unsigned not null default 0 -- 定义带有unsigned属性的类型
);
复制代码

实验1效果图:

BIT[(M)]

位数据类型,M表示存储的值的位数,范围从1-64,默认值是1

# 实验2: 注意位数据类型的加减,需要谨慎使用
create table bittest (
    a bit(8)
)

insert into bittest set a=b'00111001';

# a是ASCII码为57的字符“9”, 但是a+0的数字加减场景中,得到的是57。
mysql> select a, a+0 from bittest;
+------+------+
| a    | a+0  |
+------+------+
| 9    |   57 |
+------+------+
复制代码

BOOL/BOOLEAN

TINYINT(1)类型的别名,它有两个值,TRUE和FALSE。

该类型的逻辑判断方式跟常见的不太一样,详情参见:dev.mysql.com/doc/refman/…

INT(整数)

存储的值为整数,包含五个子类:TINYINT / SMALLINT / INT / MEDIUMINT / BIGINT, 存储的范围见下图:

INT(11) 表示的含义:
前面讲过,如果是整数类型,括号里面的11,只表示该类型显示的最大长度,与实际存储的范围无关,可通过实验3证明。

# 实验3
# 创建表(必须加上zerofill,才能看到有没有补零)
create table test_int_length (
    id int(11) not null primary key,
    int_four1 int(4) not null default 0,
    int_four2 int(4) zerofill not null default 0,
    int_eleven int(11) zerofill not null default 0
);

# 插入数据
insert into test_int_length (id, int_four1, int_four2) values (1, 1, 1);
insert into test_int_length (id, int_four1, int_four2) values (2, 100000, 100000);

# 可以看到当插入数据为1, 不足4位,会自动补零
mysql> select * from test_int_length;
+----+-----------+-----------+-------------+
| id | int_four1 | int_four2 | int_eleven  |
+----+-----------+-----------+-------------+
|  1 |         1 |      0001 | 00000000000 |
|  2 |    100000 |    100000 | 00000000000 |
+----+-----------+-----------+-------------+
2 rows in set (0.00 sec)

复制代码

DECIMAL[(M[,D])]

MySQL提供两种类型(FLOAT和DOUBLE)来存储浮点数(小数),当存储的类型在规定的范围内时,这两种类型存储的值是精确的,但如果超过规定的范围,则超过的部分会被截断和四舍五入,导致精度缺失。

为了使存储的浮点数不会丢失精度,MySQL提供了DECIMAL这种类型。其中,M表示总位数(不包括小数点和负数前面的横杠),D表示小数点后的位数。

M最大值为65,默认值是10; D最大值是30,默认值是0

DEC VS NUMERIC VS FIXED
可能有时候我们会看到这三种类型,其实这三种类型都是DECIMAL的别名。

注: 除了可以用DECIMAL类型来保证存储的小数是精确的,还有另外一种取巧的办法,使用整数类型存储,在最终显示的时候除以对应的值即可,比如1.777 = 1777 / 1000。

FLOAT[(M,D)]

单精度浮点型。存储的范围为(-3.402823466E+38,-1.175494351E-38), 0, 和 (1.175494351E-38 to 3.402823466E+38),因为不可能存储无限位小数,所以只能无限接近于0。

M是总位数,D是小数点后的位数。如果省略M和D,则将值存储到硬件允许的极限。单精度浮点数的精度约为小数点后7位。

FLOAT(p)
一种特殊的表现形式,当p的范围是0-24时,表示使用FLOAT类型;当p的范围是25-63时,表示使用DOUBLE类型。

DOUBLE[(M,D)]

单精度浮点型。存储的范围为(-1.7976931348623157E+308,-2.2250738585072014E-308), 0, 和 (2.2250738585072014E-308 to 1.7976931348623157E+308)

M是总位数,D是小数点后的位数。如果省略M和D,则将值存储到硬件允许的极限。单精度浮点数的精度约为小数点后15位。

REAL / DOUBLE PRECISION
这两种类型是DOUBLE类型的别名,不过如果“REAL_AS_FLOAT”这个参数设置为enabled, REAL代表的是FLOAT类型。

时间类型

MySQL的时间类型包含五个具体类型: DATE, TIME, DATETIME, TIMESTAMP, and YEAR。其中,TIME、TIMESTAMP、DATETIME允许精确到微妙,即小数点后6位。

五种类型的存储空间:

fractional seconds strorage表示小数点后的数字占用的字节

图片名称

举例: TIME(0), TIME(2), TIME(4) 和 TIME(6) 分别占用 3, 4, 5, and 6 bytes。

DATE

支持的范围 '1000-01-01' - '9999-12-31'。虽然DATE类型使用'YYYY-MM-DD'格式展示,但是它支持数值或者字符串的输入方式,举例如下。

# 建表
create table datetest (
    date1 DATE,
    datetime1 DATETIME,
    datetime2 DATETIME(3),
);
# 插入数据(数值或者字符串)
mysql> insert into datetest set date1=20191128;
mysql> insert into datetest set date1='20191128';
mysql> insert into datetest set date1='2019-11-28';

# 插入当前时间
mysql> insert into datetest set date1=CURDATE();
复制代码

DATETIME[(fsp)]

支持的范围 '1000-01-01 00:00:00.000000' - '9999-12-31 23:59:59.999999', 默认值是NULL,同样也支持数值或者字符串的输入方式,举例如下。

由于DATETIME没有时区的概念,默认时间是UTC时间,如果想要改为北京时间,则需要修改time_zone参数

查看:

show global variables like '%time_zone%';

修改:
blog.csdn.net/iris_xuting…

实验:

create table datetest (
    date1 DATE,
    datetime1 DATETIME,
    datetime2 DATETIME(3),
);

# 插入数据(数值或者字符串)
mysql> insert into datetest set datetime1='2019-11-28 12:00:00';
mysql> insert into datetest set datetime1=20191128120000;

# 插入当前时间
mysql> insert into datetest set datetime1=CURDATE();

# datetime和timestamp都可以设置自动初始化和更新时间
create table datetest2 (
    datetime1 DATETIME DEFAULT CURRENT_TIMESTAMP, -- 只是自动初始化,当数据改变时时间不会改变
    datetime2 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 当数据改变时时间会随之改变
    datetime3 DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)  -- 当指定精度时,DEFAULT 和 ON UPDATE也要指定相同精度
);

复制代码

TIMESTAMP[(fsp)]

支持的范围 '1970-01-01 00:00:01.000000' - '2038-01-19 03:14:07.999999', 这里的时间范围基于UTC时间。另外,TIMESTAMP列的默认值不能为NULL

需要注意的是,TIMESTAMP的表现形式基于 explicit_defaults_for_timestamp 参数,默认值是OFF(5.7版本),不开启。

如果该参数为ON,则类型为TIMESTAMP的column不会自动初始化和更新时间。

如果该参数为OFF且有多个列的类型都为TIMESTAMP,只有第一个列的时间会自动更新,其他列不会(除非加上 DEFAULT 和 ON UPDATE)

# 创建表
create table datetest3 (
    id int(11) not null default 0,
    timestamp1 TIMESTAMP,
    timestamp2 TIMESTAMP not null default '2018-10-01 00:00:00'
);

# 测试
mysql> insert into datetest3 set id=1;

mysql> select * from datetest3;
+----+---------------------+---------------------+
| id | timestamp1          | timestamp2          |
+----+---------------------+---------------------+
|  1 | 2019-11-30 12:30:58 | 2018-10-01 00:00:00 |
+----+---------------------+---------------------+
复制代码

TIMESTAMP的时间范围为什么只到2038?
www.jianshu.com/p/b222dcc47…

TIMESTAMP的时区问题
www.cnblogs.com/gaogao67/p/…
www.cnblogs.com/deverz/p/98…
blog.csdn.net/sinat_26594…
segmentfault.com/a/119000001…

注:对于有夏令时的地区,本地时间等于UTC时间 + 时区偏移 + 夏令时偏移

TIME[(fsp)]

支持的范围 '-838:59:59.000000' - '838:59:59.000000', 这种类型一般比较少用,除非你只需要当天的时间。

YEAR[(4)]

存储年份,一般也比较少用,除非你只需要存储年份。

MySQL之前的版本还支持YEAR(2)类型,不过这个类型将在MySQL5.7.5中废弃

字符串类型

MySQL的字符串类型包含以下具体类型: CHAR, VARCHAR, TEXT, ENUM和SET。

特性:
1.可以单独对类型为字符串的列设置字符集(CHARSET / CHARACTER SET)

CREATE TABLE t
(
    c1 VARCHAR(20) CHARSET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
复制代码

2.可以对整张表设置字符集

CREATE TABLE t2
(
    c1 VARCHAR(20) CHARSET utf8,
    c2 TEXT
) CHARSET utf8mb4;

mysql> show full columns from t2;
+-------+-------------+--------------------+------+-----+---------+
| Field | Type        | Collation          | Null | Key | Default |
+-------+-------------+--------------------+------+-----+---------+
| c1    | varchar(20) | utf8_general_ci    | YES  |     | NULL    |
| c2    | text        | utf8mb4_general_ci | YES  |     | NULL    |
+-------+-------------+--------------------+------+-----+---------+
复制代码

3.字符集别名
ASCII ==> CHARACTER SET latin1
UNICODE ==> CHARACTER SET ucs2

4.二进制类型 VS 文本类型
blog.csdn.net/u014465934/…

CHAR[(M)]

该类型存储的是定长字符串,与数值类型不同,M表示存储的最大字符数, M的默认值为1,最大值为255。对于utf8字符集来说,一个字母和一个汉字都算是一个字符

注: 这里要注意存储字符(characters)和存储字节(bytes)的区别,比如CHAR(5), 它最大存储5个字符,如果使用的是utf8字符集,存储的字节是5 * 3字节=15字节。

create table char_test (
    id int(11) not null default 0,
    char1 char(10) charset utf8 not null default ''
);

-- 无法插入,超出存储范围
insert into char_test set char1="abcdefghijkl";
insert into char_test set char1="一二三四五六七八九十十一";

-- 可以插入
insert into char_test set char1="abcdefghij";
insert into char_test set char1="一二三四五六七八九十";

复制代码

注: NCHAR、NATIONAL CHARACTER 是 CHAR CHARACTER SET utf8 的别名

VARCHAR(M)

该类型存储的是非定长字符串,即有可能用1字节,2字节或者3字节等存储一个字符。M也表示存储的最大字符数,M的范围是0-65535。

这里也要注意下字节和字符的区别,同CHAR。

VARCHAR(255)的由来
1.虽然VARCHAR最大能够存储65535个字符,但文档同时也提到MySQL表的每一行也是有大小限制的,非常巧,这个数字是65535字节

2.通过计算,如果varchar使用的是utf8字符集,则每一行存储的最大字符数为65535/3=21845; 如果使用utf8mb4,则最大字符数为65535/4=16383。

但上面的计算方式其实是一种理想化的计算,实际上还要减去一些额外的字节,有两种计算方式: 1.如果包含一个字段id,且类型为int,则存储的字符为(65535-1-2-4)/3 = 21843 字符。

2.如果不包含字段id,则存储的字符为(65535-1-2)/3 = 21844 字符。

  • 减1的原因是实际行的存储从第2个字节开始.
  • 减2的原因是varchar头部的2个字节表示长度.
  • 除以3的原因是一个utf8字符占用3个字节.(如果是utf8mb4要除以4)

那是不是就可以定义varchar(21844)呢? 确实可以,但是这样不现实,因为你不只这一列,其他列也需要占用字节。

3.varchar(255)的由来是因为InnoDB存储引擎的表索引的前缀长度最长是767字节(bytes),起因是2^8×3-1。767表示3个字符最大占用空间(utf8)。

所以,如果需要建索引,就不能超过 767 bytes,utf8编码时 255*3=765bytes,恰恰是能建索引情况下的最大值。

总而言之,255是一个是保证能少出错的一个很好的默认值

# 实验
mysql> create table varchar_test (
    ->     id int(11) not null default 0,
    ->     varchar1 varchar(21843) not null default ''
    -> ) charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> create table varchar_test1 (
    ->     id int(11) not null default 0,
    ->     varchar1 varchar(21844) not null default ''
    -> ) charset utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table varchar_test3 (
    ->     varchar1 varchar(21844) not null default ''
    -> ) charset utf8;
Query OK, 0 rows affected (0.01 sec

复制代码


CHAR VS VARCHAR

  • 1.CHAR存储的是定长字符串,VARCHAR存储的是不定长字符串,假设使用的utf8字符集,则CHAR类型的所有值都用三个字节存储,而VARCHAR有可能使用1字节,2字节,3字节,具体看存储的字符串,所以相对来说,VARCHAR更省磁盘空间
  • 2.如果字符串尾部带有空格,CHAR类型默认会去掉,而VARCHAR类型不会。

utf8 VS utf8mb4

  • 1.MySQL的utf8字符集并不等同广义的UTF-8字符集,utf8mb4才是
  • 2.utf8字符集每个字符最多使用三个字节,utf8mb4 字符集每个字符最多使用四个字节
  • 3.utf8mb4 是utf8的超集,包含其缺失的一些字符

BINARY[(M)]

与CHAR类型相似,但存储的是二进制数据(二、八、十六进制也可以),M表示存储的最大字节,默认为1

VARBINARY(M)

与VARCHAR类型相似,但存储的是二进制数据,M表示存储的最大字节。

BLOB

该类型主要用于存储比较大的二进制文件,包含四个具体类型: TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB。

TEXT

该类型主要用于存储比较大的文本,包含四个具体类型: TINYTEXT / TEXT / MEDIUMTEXT / LONGTEXT。

BLOB和TEXT存储占用的字节(L表示可存储的字节,实际占用的字节比L大,因为额外的字节被用于表示长度)

注:由于类型较多,所以只讲述了比较重要的类型,json类型有精力会继续补充!(本文未经允许,不可转载!)

参考:
segmentfault.com/q/101000000…

www.dazhuanlan.com/2019/10/15/…

m.w3cschool.cn/mysql/mysql…

dev.mysql.com/doc/refman/…

dev.mysql.com/doc/refman/…

关注下面的标签,发现更多相似文章
评论