1、数值类型
1.1、数值类型分类
-
严格数值类型(
INTEGER
、SMALLINT
、DECIMAL
和NUMERIC
) -
近似数值数据类型(
FLOAT
、REAL
和DOUBLE PRECISION
)
MySQL中数值类型:
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT
|
1 |
有符号-128
无符号 0
|
有符号 127
无符号 255
|
SMALLINT
|
2
|
有符号-32768
无符号 0
|
有符号 32767
无符号 65535
|
MEDIUMINT
|
3
|
有符号-8388608
无符号 0
|
有符号 8388607
无符号 1677215
|
INT、INTEGER
|
4 |
有符号-2147483648
无符号 0
|
有符号 2147483647
无符号 4294967295
|
BIGINT
|
8 |
有符号-9223372036854775808
无符号 0
|
有符号 9223372036854775807
无符号 18446744073709551615
|
浮点数类型
|
字节
|
最小值 | 最大值 |
FLOAT
|
4
|
±1.175494351E-38
|
±3.402823466E+38
|
DOUBLE
|
8 |
±2.2250738585072014E-308
|
±1.7976931348623157E+308
|
定点数类型
|
字节 | 描述 | |
DEC(M,D),
DECIMAL(M,D)
|
M+2
|
最大取值范围与 DOUBLE 相同,给定 DECIMAL 的有效取值范围由 M 和 D
决定
|
|
位类型
|
字节
|
最小值 | 最大值 |
BIT(M)
|
1~8
|
BIT(1)
|
BIT(64)
|
1.1.1、浮点数
浮点数如果不写精度和标度,则会按照实际精度值显示,如果有精度和标度,则会自动将四舍五入后的结果插入,系统不会报错;定点数如果不写精度和标度,则按照默认值 decimal(10,0)
来进行操作,并且如果数据超越了精度和标度值,系统则会报错。
1.1.2、位类型
BIT(位)类型:范围从1-64,如果不写,默认为1位,对于此字段,直接使用select命令将不会看到结果,可以用bin()(显示为二进制格式)或者hex()(显示为16进制格式)函数进行读取
例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> desc t2; + -------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + -------+--------+------+-----+---------+-------+ | id | bit (1) | YES | | NULL | | + -------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> insert into t2 values (1); Query OK, 1 row affected (0.05 sec) mysql> select * from t2; + ------+ | id | + ------+ | | + ------+ 1 row in set (0.00 sec) mysql> select bin(id),hex(id) from t2; + ---------+---------+ | bin(id) | hex(id) | + ---------+---------+ | 1 | 1 | + ---------+---------+ 1 row in set (0.03 sec) |
bit
类型数据插入时,首先将值转换为二进制,如果允许,则进行插入,如果位数小于实际定义的位数,则插入失败。
例:如果在刚刚的那张表中插入2,实际转换为二进制为10,超出了bit(1)
的实际定义位数,就会报异常,将id定义为bit(2),在插入,就会成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> insert into t2 values (2); ERROR 1406 (22001): Data too long for column 'id' at row 1 mysql> alter table t2 modify id bit (2); Query OK, 1 row affected (0.67 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 values (2); Query OK, 1 row affected (0.03 sec) mysql> select bin(id),hex(id) from t2; + ---------+---------+ | bin(id) | hex(id) | + ---------+---------+ | 1 | 1 | | 10 | 2 | + ---------+---------+ 2 rows in set (0.00 sec) |
1.1.3、时间日期类型
1.1.3、时间日期类型
日期和时间类型 | 字节 | 最小值 | 最大值 |
DATE
|
4
|
1000-01-01
|
9999-12-31
|
DATETIME
|
8
|
1000-01-01 00:00:00
|
9999-12-31 23:59:59
|
TIMESTAMP
|
4 |
19700101080001
|
2038 年的某个时刻
|
TIME
|
3
|
-838:59:59
|
838:59:59
|
YEAR
|
1 | 1901 |
2155
|
-
DATE
:用来表示年月日 -
DATETIME
:用来表示年月日时分秒(支持插入格式:2007-9-3 12:10:10'、'2007/9/3 12+10+10'、'20070903121010'、20070903121010 等) -
TIME
:只用来表示时分秒 -
TIMESTAMP
:经常插入或者更新日期为当前系统时间 -
YEAR
:表示年
例1:创建时间表(dt),字段分别为 date、time、datetime 三种日期类型,并插入数值,查看显示结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> create table dt(d date ,t time ,dt datetime); Query OK, 0 rows affected (0.23 sec) mysql> insert into dt values (now(),now(),now()); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> select * from dt; + ------------+----------+---------------------+ | d | t | dt | + ------------+----------+---------------------+ | 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 | + ------------+----------+---------------------+ 1 row in set (0.00 sec) |
例2:创建测试表t,字段id1为TIMESTAMP类型,插入空值,并显示
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> create table t(id1 timestamp ); Query OK, 0 rows affected (0.22 sec) mysql> insert into t values ( null ); Query OK, 1 row affected (0.05 sec) mysql> select * from t; + ---------------------+ | id1 | + ---------------------+ | 2021-05-13 10:18:05 | + ---------------------+ 1 row in set (0.00 sec) |
可以发现,系统给 id1 自动创建了默认值 CURRENT_TIMESTAMP
(系统日期)。(注意,MySQL
只给表中的第一个TIMESTAMP
字段设置默认值为系统日期,如果有第二个TIMESTAMP
类型,则默认值设置为0值)
例3:解释如上说明
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> alter table t add column id2 timestamp ; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t \G; *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified |
1.1.4、字符串类型
字符串类型 | 字节 | 描述及存储需求 |
CHAR(M)
|
M |
M 为 0~255 之间的整数
|
VARCHAR(M)
|
M 为 0~65535 之间的整数,值的长度+1 个字节
|
|
TINYBLOB
|
允许长度 0~255 字节,值的长度+1 个字节
|
|
BLOB
|
允许长度 0~65535 字节,值的长度+2 个字节
|
|
MEDIUMBLOB
|
允许长度 0~167772150 字节,值的长度+3 个字节
|
|
LONGBLOB
|
允许长度 0~4294967295 字节,值的长度+4 个字节
|
|
TINYTEXT
|
允许长度 0~255 字节,值的长度+2 个字节
|
|
TEXT
|
允许长度 0~65535 字节,值的长度+2 个字节
|
|
MEDIUMTEXT
|
允许长度 0~167772150 字节,值的长度+3 个字节
|
|
LONGTEXT
|
允许长度 0~4294967295 字节,值的长度+4 个字节
|
|
VARBINARY(M)
|
允许长度 0~M 个字节的变长字节字符串,值的长度+1 个字节
|
|
BINARY(M)
|
M |
允许长度 0~M 个字节的定长字节字符串
|
-
CHAR
:固定长度,在检索时,会删除尾部的空格 -
VARCHAR
:可变长字符串,在检索时,会保留这些空格
例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> create table varc(v varchar (4),c char (4)); Query OK, 0 rows affected (0.20 sec) mysql> insert into varc values ( 'abc ' , 'abc ' ); Query OK, 1 row affected (0.03 sec) mysql> select length(v),length(c) from varc; + -----------+-----------+ | length(v) | length(c) | + -----------+-----------+ | 4 | 3 | + -----------+-----------+ 1 row in set (0.01 sec) mysql> select concat(v, '+' ),concat(c, '+' ) from varc; + ---------------+---------------+ | concat(v, '+' ) | concat(c, '+' ) | + ---------------+---------------+ | abc + | abc+ | + ---------------+---------------+ 1 row in set (0.00 sec) |
-
BINARY
:类似于char
,但他是二进制字符串 -
VARBINARY
:类似于varchar
,单他是存储二进制字符串
例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> create table bina(c binary (3)); Query OK, 0 rows affected (0.22 sec) mysql> insert into t set c= 'a' ; ERROR 1054 (42S22): Unknown column 'c' in 'field list' mysql> insert into bina set c= 'a' ; Query OK, 1 row affected (0.05 sec) mysql> select *,hex(c),c= 'a' ,c= 'a\0' ,c= 'a\0\0' from bina; + ------+--------+-------+---------+-----------+ | c | hex(c) | c= 'a' | c= 'a\0' | c= 'a\0\0' | + ------+--------+-------+---------+-----------+ | a | 610000 | 0 | 0 | 1 | + ------+--------+-------+---------+-----------+ 1 row in set (0.00 sec) |
可以发现,当保存 BINARY
值时,在值的最后通过填充“0x00”(零字节)以达到指定的字段定义长度。从上例中看出,对于一个 BINARY(3)
列,当插入时'a'变为'a\0\0'
1.1.5、ENUM 类型
ENUM
:对 1~255 个成员的枚举需要 1 个字节存储;对于 255~65535 个成员,需要 2 个字节存储。
例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> create table gend(gender enum( 'M' , 'F' )); Query OK, 0 rows affected (0.20 sec) mysql> insert into gend values ( 'M' ),( 'F' ),( '1' ),( null ); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from gend; + --------+ | gender | + --------+ | M | | F | | M | | NULL | + --------+ 4 rows in set (0.00 sec) |
由此可以看出,枚举类型的插入值得时候,是忽略大小写的,初始下标从1开始;查询的时候会转换为大写,也可以插入空(null)
1.1.6、SET类型
SET
:字符串对象,里面可以包含64个成员,成员不同,存储上也有所不同
1~8 成员的集合,占 1 个字节。
9~16 成员的集合,占 2 个字节。
17~24 成员的集合,占 3 个字节。
25~32 成员的集合,占 4 个字节。
33~64 成员的集合,占 8 个字节。
Set
和EMUN
区别在于,Set一次可以选则多个成员,而ENUM则只能选择一个
例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> create table st(col set ( 'a' , 'b' , 'c' , 'd' )); Query OK, 0 rows affected (0.20 sec) mysql> insert into st values ( 'a,b' ),( 'a,d,a' ),( 'a,c' ),( 'a' ); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into st values ( 'f' ); ERROR 1265 (01000): Data truncated for column 'col' at row 1 mysql> insert into st values ( null ); Query OK, 1 row affected (0.03 sec) mysql> select * from st; + ------+ | col | + ------+ | a,b | | a,d | | a,c | | a | | NULL | + ------+ 5 rows in set (0.00 sec) |
由此可以看出,set是可以插入多个成员的,也可以插入null,插入不存在的定义列时,会报异常插入失败
原文链接:https://www.cnblogs.com/19930521zhang/p/14756392.html