mysql 数据类型

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 查看一下mysql版本mysql> select version();+------------+| version() |+------------+| 5.

查看一下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: 0

mysql> alter table t1 modify in2 int(5) zerofill;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 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: 0

mysql> alter table t1 modify id2 double;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify id3 decimal;
Query OK, 2 rows affected, 2 warnings (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> 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.frm
365M    t.ibd

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

mysql> delete from t where id=1;
Query OK, 262144 rows affected (1.29 sec)

# du -sh t.*
12K     t.frm
365M    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.frm
237M    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: 0

mysql> desc select * from t where context like 'beijing%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: idx_blob
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL 存储
【MySQL】——数据类型及字符集
【MySQL】——数据类型及字符集
165 0
【MySQL】——数据类型及字符集
|
2月前
|
机器学习/深度学习 存储 关系型数据库
MySQL数据库:常用数据类型
【2月更文挑战第7天】
29 0
|
3月前
|
SQL 关系型数据库 MySQL
学习MySQL,创建表,数据类型
学习MySQL,创建表,数据类型
|
3月前
|
设计模式 NoSQL Java
常用的设计模式以及操作Redis、MySQL数据库、各种MQ、数据类型转换的方法
常用的设计模式以及操作Redis、MySQL数据库、各种MQ、数据类型转换的方法
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
99 1
|
17天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
3月前
|
存储 SQL 关系型数据库
MySql中有哪些数据类型(数据库学习——三)
MySql中有哪些数据类型(数据库学习——三)
37 1
|
1月前
|
存储 JSON 关系型数据库
【mysql】—— 数据类型详解
【mysql】—— 数据类型详解
|
1月前
|
关系型数据库 MySQL
Mysql基础第四天,Mysql数据类型
Mysql基础第四天,Mysql数据类型
25 0
Mysql基础第四天,Mysql数据类型
|
2月前
|
存储 关系型数据库 MySQL