Mysql数据类型

最新推荐文章于 2023-11-07 17:19:48 发布

原创最新推荐文章于 2023-11-07 17:19:48 发布 · 241 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

7 篇文章

订阅专栏

本文深入解析MySQL中的数据类型，包括数值型、字符串、日期和时间类型。探讨了各种类型的使用场景，如整数、浮点数、zerofill、日期、时间戳、字符串、char、varchar、binary、enum和set等。

Mysql数据类型

Mysql数据类型

Mysql数据类型

Mysql提供了多种数据类型，主要有数值型、字符串类型、日期和时间类型。不同的Mysql版本可能支持的数据类型可能会不同。

create database mysqlstu charset=utf8;
grant all privileges on mysqlstu.*  to chengdu@localhost;

数值类型

数值类型分为整数类型、浮点数类型、定点数类型、位类型。对于整数类型数据，MySQL支持在类型名称后面的小括号内指定显示宽度，如果不显示指定宽度则默认为int(11)。

整数类型

mysql> use mysqlstu;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table intdata (
    -> field1 tinyint,
    -> field2 smallint,
    -> field3 mediumint,
    -> field4 int,
    -> field5 bigint ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql> desc intdata;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| field1 | tinyint(4)   | YES  |     | NULL    |       |
| field2 | smallint(6)  | YES  |     | NULL    |       |
| field3 | mediumint(9) | YES  |     | NULL    |       |
| field4 | int(11)      | YES  |     | NULL    |       |
| field5 | bigint(20)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.06 sec)

mysql> insert into intdata(field1, field2, field3, field4, field5) values(-128,
-32768, -8388608, -2147483648,-9223372036854775808);
Query OK, 1 row affected (0.12 sec)

mysql> select * from intdata;
+--------+--------+----------+-------------+----------------------+
| field1 | field2 | field3   | field4      | field5               |
+--------+--------+----------+-------------+----------------------+
|   -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+--------+--------+----------+-------------+----------------------+
1 row in set (0.00 sec)

mysql> insert into intdata(field1, field2, field3, field4, field5) values(-129,
-32768, -8388608, -2147483648,-9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'field1' at row 1

mysql> insert into intdata(field1, field2, field3, field4, field5) values(127, 3
2767, 8388607, 2147483647, 9223372036854775807);
Query OK, 1 row affected (0.06 sec)

mysql> select * from intdata;
+--------+--------+----------+-------------+----------------------+
| field1 | field2 | field3   | field4      | field5               |
+--------+--------+----------+-------------+----------------------+
|   -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
|    127 |  32767 |  8388607 |  2147483647 |  9223372036854775807 |
+--------+--------+----------+-------------+----------------------+
2 rows in set (0.00 sec)

mysql> insert into intdata(field1, field2, field3, field4, field5) values(128, 3
2767, 8388607, 2147483647, 9223372036854775807);
ERROR 1264 (22003): Out of range value for column 'field1' at row 1

浮点数类型

mysql> create table floatdata (
    -> field1 float(6,2),
    -> field2 double(7,3)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> desc floatdata;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | float(6,2)  | YES  |     | NULL    |       |
| field2 | double(7,3) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

zerofill

mysql> create table zerofilltable (
    -> field1 int ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into zerofilltable(field1) values(2);
Query OK, 1 row affected (0.08 sec)

mysql> select * from zerofilltable;
+--------+
| field1 |
+--------+
|      2 |
+--------+

mysql> alter table zerofilltable modify field1 int zerofill;
Query OK, 1 row affected (0.22 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from zerofilltable;
+------------+
| field1     |
+------------+
| 0000000002 |
+------------+
1 row in set (0.00 sec)

日期和时间类型

MySQL中有多种数据类型可以用于表示时间
如果要用来表示年月日，通常用DATE来表示
如果要用来表示年月日时分秒，通常用DATETIME表示
如果只用来表示时分秒，通常用TIME表示

mysql> create table datetype (
    -> field1 date,
    -> field2 datetime,
    -> field3 timestamp,
    -> field4 time,
    -> field5 year ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> desc datetype
    -> ;
+--------+-----------+------+-----+---------+-------+
| Field  | Type      | Null | Key | Default | Extra |
+--------+-----------+------+-----+---------+-------+
| field1 | date      | YES  |     | NULL    |       |
| field2 | datetime  | YES  |     | NULL    |       |
| field3 | timestamp | YES  |     | NULL    |       |
| field4 | time      | YES  |     | NULL    |       |
| field5 | year(4)   | YES  |     | NULL    |       |
+--------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-23 08:59:07 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into datetype(field1,field2,field3,field4,field5) values(now(),now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------
----------+
| Level | Code | Message
          |
+-------+------+----------------------------------------------------------------
----------+
| Note  | 1292 | Incorrect date value: '2018-11-23 09:00:20' for column 'field1'
 at row 1 |
+-------+------+----------------------------------------------------------------
----------+
1 row in set (0.00 sec)

mysql> select * from datetype;
+------------+---------------------+---------------------+----------+--------+
| field1     | field2              | field3              | field4   | field5 |
+------------+---------------------+---------------------+----------+--------+
| 2018-11-23 | 2018-11-23 09:00:20 | 2018-11-23 09:00:20 | 09:00:20 |   2018 |
+------------+---------------------+---------------------+----------+--------+
1 row in set (0.00 sec)

TIMESTAMP 和时区相关，当插入日期的时，会先转换为本地时区后存放，而从数据库里面取出来时候，也需要将日期转换为本地时区后显示。

mysql> show warnings;
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Level   | Code | Message

                               |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, b
ut will be an alias for UTF8MB4 in a future release. Please consider using UTF8M
B4 in order to be unambiguous. |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

mysql> create table timestamptest (
    -> field1 timestamp default current_timestamp,
    -> field2 datetime ) charset=utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql> desc timestamptest;
+--------+-----------+------+-----+-------------------+-------+
| Field  | Type      | Null | Key | Default           | Extra |
+--------+-----------+------+-----+-------------------+-------+
| field1 | timestamp | YES  |     | CURRENT_TIMESTAMP |       |
| field2 | datetime  | YES  |     | NULL              |       |
+--------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into timestamptest(field1, field2) values(now(), now());
Query OK, 1 row affected (0.10 sec)

mysql> select * from timestamptest;
+---------------------+---------------------+
| field1              | field2              |
+---------------------+---------------------+
| 2018-11-23 09:35:29 | 2018-11-23 09:35:29 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +09:00 |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from timestamptest;
+---------------------+---------------------+
| field1              | field2              |
+---------------------+---------------------+
| 2018-11-23 10:35:29 | 2018-11-23 09:35:29 |
+---------------------+---------------------+
1 row in set (0.00 sec)

TIMESTAMP支持的时间范围比较小， DATETIME支持的时间范围大
TIMESTAMP 插入和查询都受当地的时区影响，DATETIME 只能反映出插入时当地的时区

字符串类型

MySQL中提供了多种对字符数据的存储类型，不同版本可能有所差异。字符串类型有char、varchar、binary、varbinary、blob、text、enum、set等多种字符串类型。

CHAR和CARCHAR类型

char和varchar用来保存比较短的字符串。char固定长度，varchar列中的值为可变长字符串

mysql> create table cv(
    -> field1 char(5),
    -> field2 varchar(5)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> insert into cv(field1, field2) values('1234 ','1234 ');
Query OK, 1 row affected (0.04 sec)

mysql> select * from cv;
+--------+--------+
| field1 | field2 |
+--------+--------+
| 1234   | 1234   |
+--------+--------+
1 row in set (0.00 sec)

mysql> select length(field1), length(field2) from cv;
+----------------+----------------+
| length(field1) | length(field2) |
+----------------+----------------+
|              4 |              5 |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> insert into cv(field1, field2) values('abcdef','abcdef');
ERROR 1406 (22001): Data too long for column 'field1' at row 1

mysql> insert into cv(field1, field2) values('abcde','abcdef');
ERROR 1406 (22001): Data too long for column 'field2' at row 1
mysql> insert into cv(field1, field2) values('abcde','abcde');
Query OK, 1 row affected (0.09 sec)

在检索的时候，char列删除了尾部的空格，而varchar则保留了这些空格。

char、varchar需要指定长度

BINARY 和 VARBINARY

binary和varbinary包含二进制字符串而不包含非二进制字符串

mysql> create table binarytest (
    -> field1 binary(5),
    -> field2 varbinary(5)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> insert into binarytest(field1, field2) values('1','1');
Query OK, 1 row affected (0.03 sec)

mysql> select *,field1='1',field1='1\0\0\0\0' from binarytest;
+--------+--------+------------+--------------------+
| field1 | field2 | field1='1' | field1='1\0\0\0\0' |
+--------+--------+------------+--------------------+
| 1      | 1      |          0 |                  1 |
+--------+--------+------------+--------------------+
1 row in set (0.00 sec)

当保存BINARY值时，在值的最后面填充"0x00"(零字节)达到指定的字段定义长度。

ENUM 类型

mysql> create table enumtest (
    -> language enum('Java', 'Python', 'C++', 'C')) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Level   | Code | Message

                               |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, b
ut will be an alias for UTF8MB4 in a future release. Please consider using UTF8M
B4 in order to be unambiguous. |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

mysql> insert into enumtest(language) values('1'),('3'),(2);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from enumtest;
+----------+
| language |
+----------+
| Java     |
| C++      |
| Python   |
+----------+
3 rows in set (0.00 sec)

mysql> desc enumtest;
+----------+---------------------------------+------+-----+---------+-------+
| Field    | Type                            | Null | Key | Default | Extra |
+----------+---------------------------------+------+-----+---------+-------+
| language | enum('Java','Python','C++','C') | YES  |     | NULL    |       |
+----------+---------------------------------+------+-----+---------+-------+

set类型

set是一个集合，类似于Java语言集合，mysql用set作为字段时，该数据字段无重复元素


mysql> create table settest (
    -> filed1 set('a','b','c','d') ) charset=utf8mb4;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into settest(field1) values('a,e');
ERROR 1265 (01000): Data truncated for column 'field1' at row 1
mysql> insert into settest(field1) values('a,b');
Query OK, 1 row affected (0.06 sec)

mysql> insert into settest(field1) values('a,b'),('a,b,c,a');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from settest;
+--------+
| field1 |
+--------+
| a,b    |
| a,b    |
| a,b,c  |
+--------+
3 rows in set (0.00 sec)