AI 助理
备案控制台
开发者社区 数据库 文章 正文

mysql 数据类型

2016-09-20 996
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 查看一下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分离到单独的表中。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
索引
MySQL
Shell
存储
关键词：
云数据库 RDS MySQL 版数据类型
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
miles_wong
目录
相关文章
小强在此
|
1月前
|
关系型数据库 MySQL 数据库
MySQL数据库基础（数据库操作，常用数据类型，表的操作）
MySQL数据库基础（数据库操作，常用数据类型，表的操作）
小强在此
34 5
数据库指南针
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据类型详解及实例应用
MySQL数据类型详解及实例应用
数据库指南针
144 4
一-白
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库的数据类型、语法和高级查询
MySQL数据库的数据类型、语法和高级查询
一-白
70 0
vohelon
|
1月前
|
存储 关系型数据库 MySQL
MySQL支持多种数据类型
MySQL支持多种数据类型
vohelon
99 3
vohelon
|
1月前
|
存储 关系型数据库 MySQL
什么是mysql的数据类型？
什么是mysql的数据类型？
vohelon
47 2
vohelon
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据类型
MySQL数据类型
vohelon
52 2
cqtianxin1
|
6月前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息，使用SQL完成业务操作。SQL分为DQL（查询）、DML（增删改）、DDL（定义）、DCL（权限控制）和TCL（事务）五大类。DDL用于创建、修改和删除数据库结构，DML处理数据，DCL控制权限，TCL管理事务，DQL则用于查询数据。MySQL有多种数据类型，如数值型（整数、小数）、日期型和字符串型等，选择合适的数据类型是高效开发的关键。
cqtianxin1
69 0
蓝易云
|
3月前
|
关系型数据库 MySQL Linux
数据类型和运算符(MySQL服务器的安装,MySQL客户端,数据类型,运算符,MySQL的语法规范)
无论是对于初学者还是有经验的开发者，了解MySQL的安装、客户端使用、数据类型、运算符和语法规范都是至关重要的。这不仅有助于高效地管理和查询数据，而且对于设计和实现数据库解决方案来说是基础工作。通过深入学习和实践这些知识，您可以更好地发挥MySQL数据库的强大功能。
蓝易云
32 2
wljslmz
|
3月前
|
存储 关系型数据库 MySQL
MySQL 中的 BLOB 数据类型深入解析
【8月更文挑战第31天】
wljslmz
429 0
wljslmz
|
3月前
|
存储 关系型数据库 MySQL
全面解析：MySQL 中的字符串数据类型详解
【8月更文挑战第31天】
wljslmz
266 0

热门文章

最新文章

  • 1
    Flutter之禅 内存优化篇
  • 2
    测试开启MySQL performance_schema后对性能的影响
  • 3
    《Cadence 16.6电路设计与仿真从入门到精通》——2.4 Design Entry CIS原理图图形界面　
  • 4
    记一次对网络抖动经典案例的分析
  • 5
    国外成人网站xHamster大量用户信息黑市变卖，部分账号涉及英美政府官员
  • 6
    用户密码以BCrypt加密的方式来防范被破解
  • 7
    RDS入门——Excel文件转存到RDS数据库实践
  • 8
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 9
    MySQl复制---基于行的复制
  • 10
    mysql中char与varchar的区别分析
  • 1
    中间件MySQL性能瓶颈
    57
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    294
  • 3
    MySQL上亿数据查询优化：实践与技巧
    292
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    50
  • 5
    MySQL：union all与union区别详解
    89
  • 6
    学习mysql中使用inner join，left join 等
    50
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    95
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    142
  • 10
    【实操记录】MySQL二进制安装包部署
    193

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • PolarDB MySQL HTAP：实时数据分析加速

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云OSS设置跨域访问