博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
mysql 数据类型
阅读量:6993 次
发布时间:2019-06-27

本文共 10760 字,大约阅读时间需要 35 分钟。

查看一下mysql版本

mysql> select version();+------------+| version()  |+------------+| 5.6.27-log |+------------+1 row in set (0.01 sec)

数值类型

这里写图片描述

这里写图片描述

整数类型

创建测试表t1

mysql> create table t1 (id1 int, in2 int(5));Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1   | int(11) | YES  |     | NULL    |       || in2   | int(5)  | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)

插入测试数据

mysql> insert into t1 values(1,1);Query OK, 1 row affected (0.02 sec)mysql> select * from t1;+------+------+| id1  | in2  |+------+------+|    1 |    1 |+------+------+1 row in set (0.01 sec)

修改字段类型,加入zerofill,可以看到数值前面用字符0填充了剩余的宽度

mysql> alter table t1 modify id1 int zerofill;Query OK, 1 row affected (0.04 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> alter table t1 modify in2 int(5) zerofill;Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from t1;+------------+-------+| id1        | in2   |+------------+-------+| 0000000001 | 00001 |+------------+-------+1 row in set (0.01 sec)

测试插入大于宽度限制的值,可见宽度限制并不影响数据的正常保存

mysql> insert into t1 values(1,1111111);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------------+---------+| id1        | in2     |+------------+---------+| 0000000001 |   00001 || 0000000001 | 1111111 |+------------+---------+2 rows in set (0.00 sec)

小数类型

mysql小数表示分为:浮点数和定点数。定点数在mysql中用字符串形式存放,比浮点数精确,适用于表示货币等精度高的数据。两者都可以使用(M,D)方式表示,M:精度;D:标度。默认定点数在不指定精度时,默认会按照实际精度显示,二定点数默认整数位为10,小数位为0

创建测试表t1

mysql> create table t1 (    -> id1 float(5,2) default null,    -> id2 double(5,2) default null,    -> id3 decimal(5,2) default null);Query OK, 0 rows affected (0.03 sec)

插入数据1.23,数据都正常显示

mysql> insert into t1 values (1.23,1.23,1.23);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 |+------+------+------+1 row in set (0.00 sec)

插入数据1.234,id1、id2犹豫标度限制,舍去最后一位;id3显示被截断

mysql> insert into t1 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message                                  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+------+------+------+| id1  | id2  | id3  |+------+------+------+| 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 |+------+------+------+2 rows in set (0.00 sec)

将字段的精度及标度都去掉,插入数据1.23。可以看到id1、id2正常,id3截断。

mysql> alter table t1 modify id1 float;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 modify id2 double;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 modify id3 decimal;Query OK, 2 rows affected, 2 warnings (0.03 sec)Records: 2  Duplicates: 0  Warnings: 2mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message                                  |+-------+------+------------------------------------------+| Note  | 1265 | Data truncated for column 'id3' at row 1 || Note  | 1265 | Data truncated for column 'id3' at row 2 |+-------+------+------------------------------------------+2 rows in set (0.00 sec)mysql> desc t1;+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id1   | float         | YES  |     | NULL    |       || id2   | double        | YES  |     | NULL    |       || id3   | decimal(10,0) | YES  |     | NULL    |       |+-------+---------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t1 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t1;+-------+-------+------+| id1   | id2   | id3  |+-------+-------+------+|  1.23 |  1.23 |    1 ||  1.23 |  1.23 |    1 || 1.234 | 1.234 |    1 |+-------+-------+------+3 rows in set (0.00 sec)

通过上面的例子,可以看到浮点数如果没有精度和标度,会安装实际精度显示,如果有精度和标度,会四舍五入。定点数如果不写精度和标度,会按照默认值decimal(10,0)来进行操作,如果数据超越了精度和标度值,系统会报错。

日期时间类型

这里写图片描述

  • 根据实际需要选择最小存储的日期类型。如果只需要记录年份,南无year类型即可。
  • 如果需要记录年月日时分秒,并且记录年份比较久远,那么最好选择datetime,因为datetime比timestamp日期范围长
  • 如果日期需要让不同时区的用户使用,那么最好使用timestamp

通过测试可知,datetime为date和time的组合。

mysql> create table t (    -> d date,    -> t time,    -> dt datetime);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| d     | date     | YES  |     | NULL    |       || t     | time     | YES  |     | NULL    |       || dt    | datetime | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t values (now(),now(),now());Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t;+------------+----------+---------------------+| d          | t        | dt                  |+------------+----------+---------------------+| 2016-09-20 | 14:51:08 | 2016-09-20 14:51:08 |+------------+----------+---------------------+1 row in set (0.00 sec)

timestamp类型也可用来表示日期

mysql> create table t (id1 timestamp,id2 datetime);Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(now(),now());Query OK, 1 row affected (0.00 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2016-09-20 15:07:55 | 2016-09-20 15:07:55 |+---------------------+---------------------+1 row in set (0.00 sec)

修改时区,可见timestamp显示当地实际时间

#当前为系统时区(东八区)mysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value  |+---------------+--------+| time_zone     | SYSTEM |+---------------+--------+1 row in set (0.00 sec)#修改时区mysql> set time_zone='+9:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+---------------------+---------------------+| id1                 | id2                 |+---------------------+---------------------+| 2016-09-20 16:07:55 | 2016-09-20 15:07:55 |+---------------------+---------------------+1 row in set (0.00 sec)

字符串类型

这里写图片描述

varchar char

创建测试表vc

mysql> create table vc (v varchar(4), c char(4));Query OK, 0 rows affected (0.05 sec)

插入测试数据

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

显示查询结果

mysql> select length(v), length(c) from vc;+-----------+-----------+| length(v) | length(c) |+-----------+-----------+|         4 |         2 |+-----------+-----------+1 row in set (0.01 sec)mysql> select concat(v,'+'),concat(c,'+') from vc;+---------------+---------------+| concat(v,'+') | concat(c,'+') |+---------------+---------------+| ab  +         | ab+           |+---------------+---------------+1 row in set (0.06 sec)

可以看到char类型自动去除尾部的空格

text blob

  • 主要区别
    • text只能存字符数据,如日记
    • blob用来存二进制数据,如照片

blob和text会引起一些性能问题,特别是在执行大量删除操作时。删除操作会造成空洞,建议使用OPTIMIZE TABLE进行碎片整理。

mysql> create table t (id varchar(100),context text);Query OK, 0 rows affected (0.00 sec)mysql> insert into t values (1,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (2,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (3,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t select * from t;Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0......mysql> insert into t select * from t;Query OK, 393216 rows affected (4.05 sec)Records: 393216  Duplicates: 0  Warnings: 0

查看文件大小

# du -sh t.*12K     t.frm365M    t.ibd

删除部分数据;查看文件大小,没变化

mysql> delete from t where id=1;Query OK, 262144 rows affected (1.29 sec)# du -sh t.*12K     t.frm365M    t.ibd

对表进行OPTIMIZE

mysql> optimize table t;+--------+----------+----------+-------------------------------------------------------------------+| Table  | Op       | Msg_type | Msg_text                                                          |+--------+----------+----------+-------------------------------------------------------------------+| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead || test.t | optimize | status   | OK                                                                |+--------+----------+----------+-------------------------------------------------------------------+2 rows in set (8.34 sec)[root@db3 test]# du -sh t.*12K     t.frm237M    t.ibd

可见空洞被回收

对于blob和text字段的查询性能问题。可以使用合成索引前缀索引进行优化。

合成索引示例

mysql> create table t (    -> id varchar(100),    -> context blob,    -> hash_value varchar(40));Query OK, 0 rows affected (0.00 sec)mysql> insert into t values (1,repeat('beijing',2),md5(context));Query OK, 1 row affected (0.01 sec)mysql> insert into t values (2,repeat('beijing',2),md5(context));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (3,repeat('beijing 2008',2),md5(context));Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+--------------------------+----------------------------------+| id   | context                  | hash_value                       |+------+--------------------------+----------------------------------+| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 || 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 || 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+3 rows in set (0.00 sec)mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));+------+--------------------------+----------------------------------+| id   | context                  | hash_value                       |+------+--------------------------+----------------------------------+| 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+1 row in set (0.00 sec)

合成索引只能用于精确匹配。

使用前缀索引实现模糊查询

mysql> create index idx_blob on t(context(100));Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc select * from t where context like 'beijing%'\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t         type: ALLpossible_keys: idx_blob          key: NULL      key_len: NULL          ref: NULL         rows: 3        Extra: Using where1 row in set (0.00 sec)

避免对有blob和text字段的表进行全扫描,应尽量使用where子句并取所需字段的信息,避免造成大量的网络传输。

某些情况下,可以考虑将blob和text分离到单独的表中。

你可能感兴趣的文章
京东360buy 手机项目的“加入购物车”动画效果研究
查看>>
Spring IOC原理(二)
查看>>
webpack 打包非utf-8编码包的解决办法!
查看>>
探秘Spring AOP (四) Spring AOP 使用讲解 3
查看>>
springboot logback日志配置
查看>>
(一)MR编写之读取linux上(指定目录下的)配置文件
查看>>
springboot+jersay环境下解决long类型到前台精度丢失的问题
查看>>
K-Means聚类的一些小问题
查看>>
Java项目导出源代码jar包在Eclipse中查看中文注释乱码的问题
查看>>
SpringSecurity 学习总结
查看>>
Javascript之JS盒子模型常用的属性、定时器、获取浏览器计算后的属性样式值
查看>>
改变世界的七大NLP技术,你了解多少?(下)
查看>>
通过机器学习和人工智能实现SDN
查看>>
阿里云API网关常见应用场景
查看>>
css和javascript在IE和Firefox中二十三个不同点
查看>>
android精品源码,下拉刷新效果高德地图五子棋游戏定制日历全民TV源码
查看>>
我的友情链接
查看>>
BTREE,mysql索引
查看>>
MySQL数据库开发必备常识
查看>>
不停止MySQL服务增加从库的两种方式
查看>>