开发者社区> miles_wong> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysql 数据类型

简介: 查看一下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分离到单独的表中。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL数据库——数据类型
MySQL数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。数据库中的每个列都应该有适当的数据类型,用于限制或允许该列中存储的数据。例如,列中存储的为数字,则相应的数据类型应该为数值类型。
109 0
MySql的数据类型
在MySQL数据库中,每一条数据都有其数据类型。MySQL支持的数据类型主要分成3类:数字类型、字符串(字符)类型、日期和时间类型。 数字类型 MySQL支持所有的ANSI/ISO SQL 92数字类型。
812 0
MySql数据类型
1.整型(xxxint) MySQL数据类型 含义 tinyint(m) 1个字节表示(-128~127) smallint(m) 2个字节表示(-32768~32767) mediumint(m) 3个字节表示(-8388608~8388607) int(m)...
492 0
MySQL 数据类型
MySQL 数据类型 MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 数值类型 MySQL支持所有标准SQL数值数据类型。
634 0
MySQL 数据类型
MySQL 数据类型 MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 数值类型 MySQL支持所有标准SQL数值数据类型。
599 0
「开源人说」第二期重磅上线!一起走进《从开源中来,到开源中去》
你是否思考过开源的意义?是获取免费种子用户?还是技术贡献社会价值? 开源意义千人千面,最重要的是参与进来和分享出去。贯彻开源精神,龙蜥社区从Linux开源而来,CentOS替换出发,捐赠给开放原子这样的第三方中立开源基金会,到开源中去,最后落地千行百业中去产生实际价值。从开源中来,到开源中去,龙蜥社区曾走过一条怎样的开源之路?
171308 0
+关注
miles_wong
Java核心技术 数据存储与数据库 分布式系统与计算 系统研发与运维 编程语言
87
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载