数据类型
存储数据是处理数据的第一步 。只有正确地把数据存储起来,才能进行有效的处理和分析。否则只能是一团乱麻,无从下手。那么,怎样才能把用户各种经营相关的、纷繁复杂的数据,有序、高效地存储起 来呢? 在 MySQL 中,一个完整的数据存储过程总共有 4 步,分别是创建数据库、确认字段、创建数据 表、插入数据。
要先创建一个数据库,而不是直接创建数据表呢?
- 因为从系统架构的层次上看,MySQL数据库系统从大到小依次是【数据库服务器、数据库、数据表、数据表的行与列】。
数据类型主要给列上添加域完整性约束。开发者设置列的数据类型后, DBMS 会对添加的数据进行合法性验证,如果不合法则报错,拒绝添加
数值型
- 5种整型 tinyint、smallint、mediumint、int和bigint,主要区别就是取值范围不同,还可以在类 型前添加一个限制词unsigned,不允许添加负数
- 3种浮点型:不能精确存放float和double,可以精确存放decimal和numeric
类型名称 | 说明 |
tinyint | 1B ,取值范围 -128 到 127 |
smallint | 2B ,取值范围为 -32768 到 32767 |
mediumint | 3B ,取值范围为 -8388608 到 8388607 |
int 或者 Integer | 4B ,取值范围为 2e9 |
bigint | 8B ,取值范围为 9e18 |
float(n,m) | 4B ,单精度浮点型,取值范围 3.4e38 ,其中 n 为总位宽, m 为小数位数。要求n>=m ,如果不定义 nm 则默认提供 7-8 位的有效数据 |
double | 8B ,双精度浮点型,取值范围 1.7e308 ,总位数不超过 15 位 |
decimal 和 numeric | 采用定点存放浮点数,具体字节数取决于定义时设置的参数。如果不设置位宽,则默认 (10,0) ,总位宽最大 65 ,小数位数最大 30 |
unsigned 设置列为无符号型,只能存放大于等于 0的数据,没有负数。当使用无符号类型时取值范
围由于没有负数部分,从而导致上限扩大一倍
create table t3(id int unsigned);
mysql> insert into t3 value(-10); -- 不允许存储负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t3 value(255); -- 存储数据的上线扩大一倍
Query OK, 1 row affected (0.01 sec)
可以在类型名后添加括号,其中包含一个正整数,例如 int(5) ,这里的含义并不是要求只能存放 5 位长度的整数;含义是当进行查询时自动使用空格填充到 5 个长,如果真实数据长度大于 5 ,则按实际输出
mysql> create table t4(id int(2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 values(99999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 values(9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+-------+
| id |
+-------+
| 99999 |
| 9 |
+-------+
2 rows in set (0.00 sec)
decimal 和 numeric 作为字符串存储浮点数,可以实现浮点数的精确存储,并不是 float 和 double 种使用二进制浮点数存储。使用方法 numeric( 总位宽,小数位数 ) ,小数位数必须小于等于总位宽;小数位数最 大值 30 ,总位宽最大值为 65 ,注意可能存储的数据会超出范围,其中的符号位和小数点不占位宽,
mysql> create table t5(id numeric(5,3));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t5 values(99.999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values(-99.999); -- 位宽统计中符号位不算
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 values(100); -- 整数超出范围报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t5 values(9.1235); -- 小数位数多余则自动四舍五入
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into t5 values(9.1234);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t5;
+---------+
| id |
+---------+
| 99.999 |
| -99.999 |
| 9.124 |
| 9.123 |
+---------+
4 rows in set (0.00 sec)
int 和 numeric
- int类型不能保存小数位,存储小数时会自动进行四舍五入
mysql> create table t6(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values(99.99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t6;
+------+
| id |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
- numeric可以保存小数位,如果小数位数为0,可以模拟得到int类型的存储效果。采用的实际存储方式为字符串。查询效率远低于int
mysql> create table t7(id numeric);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t7;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+
| id | decimal(10,0) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
数值列的扩展属性
- auto_increment一般用于主键【非键列不能使用】,可以实现该列的自动生成连续整数值
mysql> create table t8(id int auto_increment,name varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t8(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t8 values(null,'yan1');-- 插入数据时不指定插入的值或者设置 插入值为null则会自动添加一个自增值
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8 values(99,'yan1'); -- 如果插入数据时不指定对应的值,则 自增长max(id)+1;如果指定对应的值,则自增效果失效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8(name) values('yan1');-- 如果不向指定列插入数据,则 auto_increment生效 Query OK, 1 row affected (0.01 sec)
mysql> select * from t8;
+-----+------+
| id | name |
+-----+------+
| 1 | k1 |
| 99 | k1 |
| 100 | k1 |
+-----+------+
3 rows in set (0.00 sec)
在使用 AUTO_INCREMENT 时,应注意以下几点:
1 、 AUTO_INCREMENT 是数据列的一种属性,只适用于整数类型数据列。
2 、设置 AUTO_INCREMENT 属性的数据列应该是一个正数序列,所以应该把该数据列声明为
UNSIGNED ,这样序列的编号个可增加一倍。
id int unsigned primary key auto_increment 1
3 、 AUTO_INCREMENT 数据列必须有唯一索引,以避免序号重复 ( 即是主键或者主键的一部分 ) 。
AUTO_INCREMENT 数据列必须具备 NOT NULL 属性。
4 、 AUTO_INCREMENT 数据列序号的最大值受该列的数据类型约束,如 TINYINT 数据列的最大编号是 127, 如加上 UNSIGNED ,则最大为 255 。一旦达到上限, AUTO_INCREMENT 就会失效。
- 当auto_incement达到上限后,再次申请下一个id时,得到的值保持不变。主键值重复报错
- 如果已经删除了部分数据,auto_increment仍旧从上次的值开始计数
5 、当进行全表删除时, MySQL AUTO_INCREMENT 会从 1 重新开始编号。这是因为进行全表操作
时, MySQL 实际是做了这样的优化操作:先把数据表里的所有数据和索引删除,然后重建数据表。
- delete from 表名称;会删除表中所有数据,但是auto_increment仍旧按照以前的计数继续生成数据
- 需要auto_increment从头开始进行计数则需要使用 truncate table 表名称 删除数据
如果想删除所有的数据行又想保留序列编号信息,可这样用一个带 where 的 delete 命令以抑制
MySQL 的优化: delete from table_name where 1;
可用 last_insert_id() 获取刚刚自增过的值
- unsigned禁用负值
- zerofill如果查询显示时,实际数据小于指定位宽,则自动添加0值
mysql> create table t9(id int(5) zerofill);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t9 values(12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+-------+
| id |
+-------+
| 00012 |
+-------+
1 row in set (0.00 sec)
mysql> insert into t9 values(123456);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+--------+
| id |
+--------+
| 00012 |
| 123456 |
+--------+
2 rows in set (0.00 sec)
- default用于设置默认值
default 只有在不插入数据时生效,如果插入数据,即使插入 null 值,仍旧不生效
mysql> create table t10(id int default 0,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t10 values(11,'yan1');-- 指定对应的数据,则default无效
Query OK, 1 row affected (0.01 sec)
mysql> insert into t10 values(null,'yan1'); -- 指定对应的数据,则default无 效,即使设置的值为null
Query OK, 1 row affected (0.01 sec)
mysql> insert into t10(name) values('yan1'); -- 只有不针对这个列进行数据插入 时default才能生效 Query OK, 1 row affected (0.00 sec)
mysql> select * from t10;
+------+------+
| id | name |
+------+------+
| 11 | k1 |
| NULL | k1 |
| 0 | k1 |
+------+------+
3 rows in set (0.00 sec)
null 是什么意思
null 的意思为未知的数据,它既不是空字符串,不是任何一个具体的值;不能把任何值与一个 null
值进行比较。因为 null 的意思是不确定的值。真正的 null 值比较必须使用特殊的运算符 is null 或者 is
not null
mysql> select null=null; -- =表示等值比较
+-----------+
| null=null |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select null!=null; -- 比较不相等
+------------+ |
null!=null |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
字符串类型
char(n) 定长字符串, n 值用于表示所允许的最大长度,取值范围 0-255 ,如果不指定长度,则默认 1
- 长度限制不区分中英文,表示是允许最多存储多少个字符
- 使用length函数获取的是字节数,不是字符数。在utf-8编码种一个汉字占用3个字节
mysql> create table c2(id char(5));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into c2 values("yan");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c2 values("中国人民好");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c2 values("中国人民好1");
ERROR 1406 (22001): Data too long for column 'id' at row 1
varchar(n) 可变长度的字符串, n 值取值范围为 0-65535 ,如果不设置 n 值则报错
- 实际存储多少个字符就占多少个位置,每个列所占用的长度根据具体内容变化
create table t1(id varchar(20));
# 具体的n值上限和所使用的编码字符集相关
mysql> create table t24(id varchar(1000000));
ERROR 1074 (42000): Column length too big for column 'id' (max = 16383); use BLOB or TEXT
instead
mysql> create table t24(id varchar(1000000)) default charset utf8; ERROR 1074 (42000):
Column length too big for column 'id' (max = 21845); use BLOB or TEXT instead
mysql> create table t24(id varchar(1000000)) default charset latin1; ERROR 1074 (42000):
Column length too big for column 'id' (max = 65535); use BLOB or TEXT instead
char 和 varchar
- char属于定长,varchar属于变长。选择最基本的依据就是定长使用char,变长使用varchar
- char允许的最大长度为255,varchar允许的最大长度为65535
- 如果存储的字符串长度变化不大,推荐优先考虑使用char,例如针对口令进行md5加密,固定长度的行不容易产生碎片;对于长度很短的列,char的性能优于varchar,是因为varchar需要引入额外一个或者两个字节存储字符串长度
- char在计算种会自动去除空格;varchar不会去掉空格,但是进行字符串比较时,会去除空格后进行比较;在插入数据时系统会自动去除字符串默认多余的空格
mysql> insert into t25 values('a ');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t25 where id='a ';
Empty set (0.00 sec)
大对象类型Lob
大对象类型可以分为2大类:文本类型和二进制类型
- BLOB是一个二进制大对象,可以容纳可变长度的数据,其中分为tinyblob、blob、 mediumblob、longblob。一般用于存储二进制数据,例如声音、图像、视频等
- Text是一个文本大对象,可以容纳可变长度的数据,其中分为tinytext、text、mediumtext、 longtext,一般用于存储文本类型数据,例如小说、商品简介
mysql> create table c9(id text);
Query OK, 0 rows affected (0.02 sec)
mysql> create table c8(id blob);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into c9 values("Abc");
Query OK, 1 row affected (0.01 sec)
mysql> insert into c8 values("Abc");
Query OK, 1 row affected (0.01 sec)
mysql> select * from c9 where id='abc';
+------+
| id |
+------+
| Abc |
+------+
1 row in set (0.00 sec)
mysql> select * from c8 where id='abc'; -- 由于二进制大对象实际上是采用二进制数据 进行存储,所以比较时会出现区分大小写的效果
Empty set (0.00 sec)
两者之间的区别:
- blob是二进制类型,可以容纳可变长度的数据,区分大小写;text是一个不区分大小写的blob
类型名称 | 允许的最大长度 | 说明 |
tinytext | 0-255 | 短文本字符串 |
text | 0-65535 | 长文本字符串 |
mediumtext | 16M | 中等长度的长文本字符串 |
longtext | 4G | 极大文本数据 |
tinyblob | 0-255 | 不超过 255 个长的二进制串 |
blob | 0-65535 | 二进制类型的长文本字符串 |
mediumblob | 16M | 中等长度的二进制长文本字符串 |
longblob | 4G | 极大二进制数据 |
日期时间类型
create table dt1(id date);
insert into dt1 values('1989-2-3');
特殊的时间是时间戳类型
create table dt2(id timestamp default current_timestamp,name varchar(20));
mysql> insert into dt3(name) values('yan1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from dt3;
+---------------------+------+
| id | name |
+---------------------+------+
| 2022-04-17 15:07:33 | k1 |
+---------------------+------+
1 row in set (0.00 sec)
时间戳字段定义主要影响两类操作:
1. 插入记录时,时间戳字段包含 DEFAULT CURRENT_TIMESTAMP ,如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
2. 更新记录时,时间戳字段包含 ON UPDATE CURRENT_TIMESTAMP ,如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
3. 当字段定义为 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP ,表示该字段在插入或更新时未指定值,则被赋值为当前时间
系统函数 now() 可以获取系统的当前时间
select now();
create table t8(id int, name varchar(32), bdate datetime default now()); 123
类型说明
类型名称 | 说明 | 格式 |
date | 日期类型,有效范围为 1000-9999 年,不包含时分秒 | 3B , yyyy-MM-dd |
time | 时间类型,有效范围为 -838 到 838 小时范围内,一般用 于表示时间值或者持续时间。不包含年月日 | 3B , HH:mm:ss |
year | 年份类型,有效范围 1901 到 2155 年 | 1B , yyyy |
datetime | 日期时间类型,其中包含日期和时间,可以精确到秒, 有效范围为 1000 到 9999 年。具体存储时和时区无关。 从 MySQL5.6.4 之后可以包含秒后的小数部分,精度最 高为微妙 (6 位 ) | 非小数部分 5B+ 小数 部分 0-3B ,如果有小 数部分则会随着小数 位数变化, yyyy-MM dd HH:mm:ss |
timestamp | 时间戳类型,可以精确到秒,有效分为是 1970 到 2038 年,同时支持自动存储修改的时间,存储有时区数据, 实际存储毫秒数。在 MySQL 5.6.4 及之后版本,可以将 时间戳类型数据最高精确微秒, timestamp(N)N 取值范 围为 0-6 | 非小数部分 4B+ 小数 部分 0-3B ,默认显示 格式 YYYY-MM-dd HH:mm:ss |
集合和枚举类型
- set集合类型,可以设置多个集合成员,允许从多个集合成员种选择多个数据进行插入
create table set1(id int,hobby set('抽烟','喝酒','烫头'));
insert into set1 values(1,''); -- 允许,同时允许插入null
insert into set1 values(1,'烫头,抽烟'); -- 没问题
insert into set1 values(1,'烫头,喝酒'); -- 没有问题,但是入库时会自动排序
insert into set1 values(1,'烫头,抽烟,扣脚'); -- 报错,因为其中一个值不在set定义种
insert into set1 values(1,'抽烟,抽烟');-- 实际上只插入一个值,也就是会自动去除重复值
- enum枚举类型,可以设置允许选用的多个值,列只能赋予某个枚举成员值
create table enum1(id int,sex enum('男','女','不确定'));
insert into enum1 values(1,''); -- 报错,但是允许插入null
insert into enum1 values(1,'男');
insert into enum1 values(2,'男,女'); -- 报错
位类型
如果直接使用 bit 则默认 bit(1)
bit(1) 表示可以存储一个二进制位,只能存储 0 或者 1 ,其中 n 的 最大上限值为 64
bit(8) 表示可以存储 8 个二进制位的值,取值范围为 0 到 255
总结
- 定义数据类型就是定义列,数据类型决定数据的特性【域完整性】
- 数据类型主要分为字符串类型、定点数和浮点数类型、日期类型、大对象类型【慎用】
- 数据类型在不同的存储引擎上表现不同
- 数据所采用的数据类型是根据业务需求和对应的数据类型特征进行选择,不建议使用过于复杂的数据类型