目录
- 1、数值类型
- 1.1、数值类型分类
- 1.1.1、浮点数
- 1.1.2、位类型
- 1.1.3、时间日期类型
- 1.1.4、字符串类型
- 1.1.5、enum 类型
- 1.1.6、set类型
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进制格式)函数进行读取
例:
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),在插入,就会成功
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 三种日期类型,并插入数值,查看显示结果
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类型,插入空值,并显示
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:解释如上说明
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
:可变长字符串,在检索时,会保留这些空格
例:
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
,单他是存储二进制字符串
例:
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='amysql> 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)',c='amysql> 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)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)' from bina; +------+--------+-------+---------+-----------+ | c | hex(c) | c='a' | c='amysql> 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)' | c='amysql> 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)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)' | +------+--------+-------+---------+-----------+ | 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 个字节存储。
例:
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则只能选择一个
例:
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,插入不存在的定义列时,会报异常插入失败