美文网首页MySQL
15-MySQL数据类型

15-MySQL数据类型

作者: 紫荆秋雪_文 | 来源:发表于2021-05-20 14:55 被阅读0次

    一、数值类型

    数值类型包括：整数类型、浮点数类型、定点数类型

    1、整数类型

    整数类型.png
    整数类型表示的数值范围.png

    2、设置字段的显示宽度

    create table t1(id int(3));

    • 整数类型的显示宽度与数据类型的取值范围无关。显示宽度只是指定最大显示的数字个数，如果在数据表中插入了大于显示宽度，但是并没有超过数据类型的数值范围，那么依然可以正确地插入数据并且能够正确的显示

    • 插入数据

    insert into t1(id) values (1), (123456);
    • 查看数据
    select * from t1;

    +--------+
| id     |
+--------+
|      1 |
| 123456 |
+--------+
    • 插入超出数据类型范围的值
    mysql> insert into t1(id) values (1), (123456), (123456789987654321);
ERROR 1264 (22003): Out of range value for column 'id' at row 3

    3、整数类型的显示宽度配合 zerofill 使用，zerofill表示在数字的显示位数不够时，可以用字符 0 进行填充

    • 创建数据表时指定zerofill
    create table t2(
  id1 int zerofill,
  id2 int(3) zerofill
);
    • 插入数据
    insert into t2(id1, id2) values(1, 2);
    • 查看数据
    select * from t2;

    +------------+------+
| id1        | id2  |
+------------+------+
| 0000000001 |  002 |
+------------+------+
    • 当指定数据字段为 zerofill 时，MySQL 会自动为当前字段添加 unsigned 属性
    • 查看指定 zerofill 的字段
    mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int(10) unsigned zerofill DEFAULT NULL,
  `id2` int(3) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    • 设置主键自动递增类型
    create table if not exists t3 ( 
  id int not null auto_increment primary key, 
  age int 
);
    • 插入数据
    insert into t3 (age) values (18), (20), (16);

    • 小结：MySQL 自动为 auto_increment 类型的整型列设置了自动递增的整数值

    • 不设置id字段为主键，而是将id设置为唯一索引

    create table if not exists t4 ( id int not null auto_increment unique, age int );
    • 插入数据
    insert into t4 (age) values (5), (7), (9);

    • 小结1：MySQL 同样为设置为 auto_increment 的整型列 id 设置了自动递增的整数值

    • 小结2：其他整数类型（bool）的用法与 int 类型相同，依然会自动递增

    4、浮点数类型

    浮点数类型主要有两种：单精度浮点数 FLOAT 和 双精度浮点数 DOUBLE 浮点数所需的存储空间.png
    浮点数表示的数值范围.png
    • 创建数据表
    create table if not exists t6 (f float, d double);
    • 插入数据
    insert into t6 (f, d) values (3.14, 5.98);
    • 再次插入数据
    insert into t6 (f, d) values (3.144444444444444, 5.98999999999999999999999);
    • 查询数据
    mysql> select * from t6;
+---------+------+
| f       | d    |
+---------+------+
|    3.14 | 5.98 |
| 3.14444 | 5.99 |
+---------+------+

    • 为 float 类型和 double 类型插入超出数据类型精度的数据时，MySQL 对插入的数据进行 四舍五入

    • 使用(M, D)方式来给浮点数设置精度

    • M：整数位和小数位一共会显示 M 位数字，称为精度

    • D：小数点后会显示的位数，称为标度

    • 创建带有（M, D）字段的数据表

    create table if not exists t7 (
  f float(5, 2), 
  d double(5, 2)
);
    • 插入数据
    insert into t7 (f, d) values (3.14, 3.14);
    • 再次插入数据
    insert into t7 (f, d) values (3.141, 3.141);
    • 查询数据
    mysql> select * from t7;
+------+------+
| f    | d    |
+------+------+
| 3.14 | 3.14 |
| 3.14 | 3.14 |
+------+------+

    5、定点数类型-DECIMAL

    • 创建数据表
    create table if not exists t8 (   d1 decimal,    d2 decimal(5, 2) );
    • decimal默认(M, D) 为 (10, 0)
    mysql> show create table t8 \G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `d1` decimal(10,0) DEFAULT NULL,
  `d2` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    • 插入数据
    insert into t8 (d1, d2) values (3.14, 3.14);
    • 查看数据
    mysql> select * from t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
+------+------+
    • 再次插入数据
    insert into t8 (d1, d2) values (3.14, 3.149);
    • 查看数据
    mysql> select * from t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
|    3 | 3.15 |
+------+------+

    二、日期和时间类型

    MySQL 提供了表示日期和时间的数据类型，主要有 YEAR类型、TIME类型、DATE类型、DATETIMEL类型和TIMESTAMP类型

    • DATE 类型通常用来表示 年 月 日
    • DATETIME 类型通常用来表示 年、月、日、时、分、秒

    • TIME 类型通常用来表示 时、分、秒


      日期和时间类型占用的存储空间.png
    日期和时间类型表示的范围.png

    1、YEAR类型

    在MySQL中，YEAR有以下几种存储格式

    • 以4位字符串或数字格式表示 YEAR 类型 ，其格式是 YYYY，最小值为 1901，最大值为 2155

    • 以2位字符串格式表示 YEAR 类型，最小值为 00， 最大值为99。

      • 当取值为00到69时，表示2000到2069
      • 当取值为70到99，表示1970到1999
      • 当插入的数据超出取值范围，则MySQL会将值自动转换为2000

    • 创建数据表

    create table if not exists t9 (y year);
    • 插入数据（数字2020 和 字符串 2020）
    insert into t9 (y) values (2020), ('2020');
    • 清空数据表中数据
    delete from t9;
    • 插入2位数数据（0，00， 88， 20）
    insert into t9 (y) values (0), (00), (88), (20);

    mysql> select * from t9;
+------+
| y    |
+------+
| 0000 |
| 0000 |
| 1988 |
| 2020 |
+------+
    • 插入2位字符串（'0'，'00'， '88'， '20'）
    insert into t9 (y) values ('0'), ('00'), ('88'), ('20');

    mysql> select * from t9;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1988 |
| 2020 |
+------+

    2、TIME类型

    在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式

    • 可以使用带有冒号的字符串

      • D：表示天，其最小值为0，最大值为34，如果使用带有D格式的字符串插入 TIME 类型的字段时，D会被转化为小时，计算格式：D*24 + HH
      • D HH:MM:SS
      • HH:MM:SS
      • HH:MM
      • D HH:MM
      • D HH 或 SS格式

    • 可以使用不带有冒号的字符串或数字

      • "HHMMSS"
      • HHMMSS
      • 插入不合法的字符串或者数字，MySQL在存储时，会将其自动转化为00:00:00进行存储

    • 使用 CURRENT_TIME或者NOW()，会插入当前系统的时间

    • 注意

      • 12:10表示 12:10:00，而不是00:12:10
      • 1210或'1210'：表示00:12:10，而不是 12:10:00

    • 创建数据表

    create table t10 (t time);
    • 插入数据
    insert into t10 (t) values ('2 12:30:29'), ('12:30:29'), ('12:40'), ('2 12:40'), ('45');

    mysql> select * from t10;
+----------+
| t        |
+----------+
| 60:30:29 |
| 12:30:29 |
| 12:40:00 |
| 60:40:00 |
| 00:00:45 |
+----------+
    • 插入字符串数字
    insert into t10 (t) values ('123520'), ('124011'), ('0');

    mysql> select * from t10;
+----------+
| t        |
+----------+
| 12:35:20 |
| 12:40:11 |
| 00:00:00 |
+----------+
    • 插入不足6位数字的字符串，先右对齐
    insert into t10 (t) values ('12352');

    mysql> select * from t10;
+----------+
| t        |
+----------+
| 12:35:20 |
| 12:40:11 |
| 00:00:00 |
| 01:23:52 |
+----------+
    • 清空数据
    delete from t10;
    • 插入数据
    insert into t10 (t) values (now()), (current_time);

    mysql> select * from t10;
+----------+
| t        |
+----------+
| 02:45:32 |
| 02:45:32 |
+----------+

    3、DATE类型

    向DATE类型的字段插入数据时，同样需要满足一定的格式条件

    • 以YYYY-MM-DD 格式或者 YYYYMMDD格式表示的字符串日期，其最小值取值为1000-01-01，最大值为9999-12-03

    • 以YY-MM-DD 格式或者 YYMMDD 格式表示字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件

    • 以YYYYMMDD格式表示数字日期，能够被转化为YYYY-MM-DD格式

    • 以YYMMDD格式表示的数字日期，同样满足年份为两位数值或字符串 YEAR 类型的格式条件

    • 使用CURRENT_DATE 或者 NOW()函数，会插入当前系统的日期

    • 创建数据表

    create table t11 (d date);
    • 插入数据
    insert into t11 (d) values ('2020-5-20'), ('20200520');

    mysql> select * from t11;
+------------+
| d          |
+------------+
| 2020-05-20 |
| 2020-05-20 |
+------------+

    4、DATETIME类型

    向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件

    • 以 YYYY-MM-DD HH:MM:SS 格式或者YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00, 最大值 9999-12-03 23:59:59

    • 以YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年费规则符号YEAR类型的规则

    • 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转换为YYYY-MM-DD HH:MM:SS格式

    • 以YYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则

    • 使用函数CURRENT_TIMESTAMP() 和 NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间

    • 创建数据表

    create table t12 (dt datetime);
    • 插入全格式字符串类型数据
    insert into t12 (dt) values ('2021-05-20 11:08:08');
insert into t12 (dt) values ('20210520110808');

    mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 11:08:08 |
| 2021-05-20 11:08:08 |
+---------------------+
    • 插入省略式字符串类型数据
    insert into t12 (dt) values ('21-05-20 11:08:08');
insert into t12 (dt) values ('210520110808');
    • 插入全格式数字类型数据
    insert into t12 (dt) values (20210520110808);
    • 插入省略格式数字类型数据
    insert into t12 (dt) values (210520110808);

    mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 11:08:08 |
| 2021-05-20 11:08:08 |
+---------------------+
    • 使用函数
    insert into t12 (dt) values (current_timestamp);
insert into t12 (dt) values (now());

    mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 03:17:59 |
| 2021-05-20 03:18:23 |
+---------------------+

    5、TIMESTAMP 类型

    • 与DATETIME类型相似，但是存储范围比 DATETIME 类型小

    三、文本字符串类型

    在MySQL中，字符串类型可以存储文本字符串数据，也可以存储一些图片、音频和视频数据，也就是二进制数据。因此在MySQL中，字符串类型可以分为文本字符串类型和二进制字符串类型。

    1、文本字符串类型

    文本字符串类型所占用存储空间.png

    2、CHAR 与 VARCHAR 类型

    • CHAR 与 VARCHAR 类型都可以存储比较短的字符串

    • CHAR类型的字段长度是固定的，检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格

    • VARCHAR类型修饰的字符串是一个可变长的字符串，检索VARCHAR类型的字段数据时，会保留数据尾部的空格

    • 创建数据表

    create table t13(vc varchar(4), c char(4));
    • 插入数据
    insert into t13 (vc, c) values ('abc', 'abc');
    • 查看数据表中字段的长度
    select length(vc), length(c) from t13;
    • 插入尾部为空格的字符串
    insert into t13 (vc, c) values ('a  ', 'a  ');

    mysql> select * from t13;
+------+------+
| vc   | c    |
+------+------+
| a    | a    |
+------+------+
    • 查看数据表中字段的长度
    mysql> select length(vc), length(c) from t13;
+------------+-----------+
| length(vc) | length(c) |
+------------+-----------+
|          3 |         1 |
+------------+-----------+

    • MySQL在检索CHAR类型的字段时，回去除尾部的空格

    • MySQL在检索VARCHAR类型的字段时，则不会去除尾部的空格

    • 追加数据

    mysql> select concat(vc, 'b'), concat(c, 'b') from t13;
+-----------------+----------------+
| concat(vc, 'b') | concat(c, 'b') |
+-----------------+----------------+
| a  b            | ab             |
+-----------------+----------------+

    3、TEXT类型

    在MySQL中，Text用来保存文件类型的字符串，共包含4种类型

    • tinytext
    • text
    • mediumtext
    • longtext

    4、ENUM类型——枚举类型

    • 创建数据表
    create table t14 (e enum('A', 'B', 'C'));
    • 插入数据
    insert into t14 (e) values ('a'), ('c');

    mysql> select * from t14;
+------+
| e    |
+------+
| A    |
| C    |
+------+
    • 插入数字下标
    insert into t14 (e) values ('1'), ('2');

    mysql> select * from t14;
+------+
| e    |
+------+
| A    |
| B    |
+------+

    5、SET类型

    SET 表示一个字符串对象，可以包含 0 个或多个成员，但成员个数的上限为64. image.png

    • SET类型在选取成员时，可以一次选择多个成员，这点与ENUM类型不同

    6、JSON类型

    在MySQL5.7中支持JSON数据类型。在MySQL8.x中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效

    • 创建数据表
    create table t16 (j JSON);
    • 插入数据
    insert into t16 (j) values ('{"name":"Raven", "age":18, "address":{"city":"nmg"}}');

    
mysql> select * from t16;
+----------------------------------------------------------+
| j                                                        |
+----------------------------------------------------------+
| {"age": 18, "name": "Raven", "address": {"city": "nmg"}} |
+----------------------------------------------------------+
    • 检索JSON类型的字段中数据
    mysql> select j->'$.name' from t16;
+-------------+
| j->'$.name' |
+-------------+
| "Raven"     |
+-------------+

    四、二进制字符串类型

    MySQL中的二进制字符串类型主要存储一些二进制数据，比如存储图片、音频和视频等二进制数据 二进制字符串类型长度与占用空间.png

    相关文章

      网友评论

        本文标题：15-MySQL数据类型

        本文链接：https://www.haomeiwen.com/subject/nojxjltx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        MySQL

        关于我们|服务条款|联系我们|15-MySQL数据类型|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！