1、整数型
类型 | 字节数 | 取值范围 |
---|---|---|
tinyint | 1字节 | 有符号值:-128到127(-27到27-1) 无符号值:0到255(0到2^8-1) |
smallint | 2字节 | 有符号值:-32768到32766(-215到215 -1) 无符号值: 0到65535 (0到2^16 -1) |
mediumint | 3字节 | 有符号值:-8388608到8388607(-223到223-1) 无符号值: 0到16777215(0到2^24-1) |
int | 4字节 | 有符号值:-2147483648到2147483647(-231到231-1) 无符号值:0到4294967295(0到2^32-1) |
bigint | 8字节 | 有符号值:-263到263-1 无符号值:0到2^64-1 |
问题1: mysql 整数型无符号
无符号值最小值是0,如果这是向这里插入负数,会出现“Out of range”错误
无符号的定义需要用到 unsigned关键字
无符号类型定义如下:
ALTER TABLE stu ADD age INT UNSIGNED;
注意:如果一个整型用了“ZEROFILL”(zerofill),那么他默认就是UNSIGNED,如下
ALTER TABLE per ADD d INT ZEROFILL;
表信息部分如下
+--------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| d | int(10) unsigned zerofill | YES | | NULL | |
+--------+---------------------------+------+-----+---------+-------+
问题2:整数型类型后面的括号表示什么?
后面的括号表示其显示最小宽度,例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度,如果不显示宽度则默认为int(11)。
他一般配合“zerofill”使用,顾名思义,zerofill就是用“0”填充的意思,也就是说在数字位数不够的空间用字符“0”填充。
案例1:创建id1和id2两个字段,指定其数值宽度为int和int(5)
+-------+------------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
+-------+------------------+------+-----+---------+-------+
| id1 | INT(11) | YES | | NULL | |
| id2 | INT(5) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
分别插入数值1,查看其数值区别,可以看到其,没有区别
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
案例2,分别插入数值1,000,000,查看其数值区别,可以看到其,没有区别,
+---------+-----------+
| id1 | id2 |
+---------+-----------+
| 1 | 1 |
| 1000000 | 1000000 |
+---------+-----------+
案例3:修改id1和id2两个字段类型,加入zerofill
mysql> alter table stu modify id1 int zerofill;
mysql> alter table stu modify id2 int(5) zerofill;
mysql> select * from stu;
+------------+-----------+
| id1 | id2 |
+------------+-----------+
| 0000000001 | 00001 |
| 0001000000 | 1000000 |
+------------+-----------+
总结:后面的括号表示其显示最小宽度,如果不加“zerofill”关键字,则没有区别,只有加“zerofill”,才会有区别
2、浮点型与定点型
类型 | 字节数 | 取值范围 |
---|---|---|
float | 4字节 | |
double | 8字节 | |
decimal | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 |
decimal最适合保存准确度要求高,而且用于计算的数据,比如价格。但是在使用decimal类型的时候,注意 长度设置。
3、位类型
位类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
BIT(M) | 1~8 | BIT(1) | BIT(64) |
M:表示最大位数,默认值是1 ,范围:1~64
BIT(M)主要作用是来存放二进值数,注意:直接使用SELECT命令将看不到结果,可以用bin()【显示为二进制格式】或者hex()【显示为十六进制格式】函数进行读取
案例1,创建 id 其类型是BIT(1)
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
插入数值1 操作如下
mysql> insert into s values (1);
Query OK, 1 row affected (0.04 sec)
使用SELECT命令,bin() 和hex()查看结果:
+-----------+-----------+------------+
| 使用bin() | 使用hex() | 使用select |
+-----------+-----------+------------+
| 1 | 1 | _ |
+-----------+-----------+------------+
数据插入bit类型时,首先转换为二进制,如果位数容许,就成功插入;如果位数小于实际定义的位数,则插入失败。
案例2 插入2
mysql> insert into s values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
因为2的二进制是10 ,所以插入不了
4、字符型
字符串类型用来存储字符串数据除了可以存储字符串数据之外还可以存储其他数据比如图片和声音的二进制数据
注意:mysql 中1个字节存储一个汉字,java 中2个字节存储一个汉字
列类型 | 字节数 | 存储需求(字节) |
---|---|---|
char(M) | M | 0<=M<=255 |
varchar(M) | 0<=M<=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个字节 |
enum | 取决于枚举值的个数(最多65535个值) | |
set | 取决于set成员的数目(最多64个成员) |
char与varchar的区别?
char与varchar很类似,他们都是来保存MySQL中较短的字符串,二者的主要区别是存储方式不同,
char列的长度固定为创建时声明的长度,长度可以是0~255的任何值
varchar的长度是可变的,在检索是char会删除尾部的空格,而varchar不会,注意:前面的空格不会删除
案例
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c | char(4) | YES | | NULL | |
| vc | varchar(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
分别插入字符“ab<空格><空格>”,查看他们的长度
mysql> select length(c),length(vc) from stu;
+-----------+------------+
| length(c) | length(vc) |
+-----------+------------+
| 2 | 4 |
+-----------+------------+
分别插入字符“<空格>ab”,查看他们的长度
mysql> select length(c),length(vc) from stu;
+-----------+------------+
| length(c) | length(vc) |
+-----------+------------+
| 3 | 3 |
+-----------+------------+
binary和varbinary的区别?
binary和varbinary类似于char和varchar,不同的是他们包含二进制字符串,而不包含非二进制字符串
enum
对于1~255个成员的枚举需要1个字节存储
对于255~65535个成员,需要2个字节存储,最多有65535个成员
注意:ENUM是忽略大小写的,如果插入的字符不在范围内,会产生错误
案例
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| en | enum('M','f') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
插入 m 和 F,查看
+------+
| en |
+------+
| M |
| f |
+------+
结论;ENUM是忽略大小写的,在存储m 和 F时,会转换其大小写
SET
SET类型和ENUM类型非常类似,里面包含0~64个字符,根据成员的不同,存储上也不同
注意:SET也忽略大小写的
1~8成员的集合,占1个字节
9~16成员的集合,占2个字节
17~24成员的集合,占3个字节
25~32成员的集合,占4个字节
33~64成员的集合,占8个字节
SET类型和ENUM除存储之外,
最大的区别是SET类型可以一次选取多个成员,而ENUM则只能选一个
案例
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| se | set('a','B','c') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
插入A 和B
mysql> insert into stu values ('A,B');
Query OK, 1 row affected (0.06 sec)
mysql> select * from stu;
+------+
| se |
+------+
| a,B |
+------+
5、日期时间型
类型 | 字节数 | 取值范围 | 表示形式 |
---|---|---|---|
year | 1字节 | 1901~2155 | YYYY |
time | 3字节 | -838:59:59~838:59:59 | HH:MM:SS |
date | 4字节 | 1000-01-01~9999-12-31 | YYYY-MM-DD |
datetime | 8字节 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS |
timestamp | 4字节 | 1970-01-01 08:00:01~2038-01-19 11:14:07 | YYYY-MM-DD HH:MM:SS |
案例,
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| y | year(4) | YES | | NULL | |
| t | time | YES | | NULL | |
| d | date | YES | | NULL | |
| dt | datetime | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
可以看出系统自动给“timestamp”类型变量,定义了一个默认值CURRENT_TIMESTAMP
我们向ts插入一个null试试,用select查看
+---------------------+
| ts |
+---------------------+
| 2018-11-24 16:49:29 |
+---------------------+
再向ts插入一个null试试,用select查看
+---------------------+
| ts |
+---------------------+
| 2018-11-24 16:49:29 |
| 2018-11-24 16:50:43 |
+---------------------+
结论1. timestamp插入默认值,系统会分配其值,哎!,这很方便!!,我们将datetime类型dt默认值改成CURRENT_TIMESTAMP
mysql> alter table stu modify dt datetime default current_timestamp;
ERROR 1067 (42000): Invalid default value for 'dt'
看来不行!!
timestamp,还有一个特点,就是和时区有关
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 2018-11-24 17:02:56 | 2018-11-24 17:02:56 |
+---------------------+---------------------+
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 2018-11-24 17:02:56 | 2018-11-24 18:02:56 |
+---------------------+---------------------+
datetime 与timestamp的区别?
- timestamp支持的时间范围较小,最大时限是2038年的某个时间,而DATETIME最大时限很大
- timestamp,插入null值时,系统会分配其值,他有默认值,如果超出其范围,mysql认为该值溢出,使用"0000-00-00 00:00:00"进行填补。
- timestamp的插入和查询的数据受当地时区的影响,更能反映实际的日期,而DATETIME只能反映出插入时的当地时间,其他时区的人查看数据必然会有误差。
- timestamp受mysql版本影响比较大