MySQL的数据类型学习笔记

MySQL的数据类型

整型

1. tinyint

大小相当于c的char类型，1个字节，它有一个别名叫bool两者大小相同

tinyint表示范围：-128 - 127tinyint unsigned表示范围：0 - 255

2. smallint

大小相当于c的short类型，2个字节

smallint表示范围：-32768 - 32767smallint unsigned表示范围：0-65535

3. mediumint

大小为3个字节
mediumint表示范围：-8388608 - 8388607
mediumint unsigned表示范围：0-16777215

4. int

大小相当于c的int，4个字节
int表示范围：-2147483648 - 2147483637
int unsigned表示范围：4294967295

5.bigint

相当于long long类型，8个字节
biging表示范围：(-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807
bigint unsigned表示范围：0~18446744073709551615

6. 测试一下：

//创建一个表有tinyint类型数据create table type ( a tinyint, b tinyint unsigned);//插入一个符合的值mysql> insert into type values(100, 200);Query OK, 1 row affected (0.40 sec)//插入不符合的值，报错。mysql> insert into type values(-129, 200);ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> insert into type values(100, -1);ERROR 1264 (22003): Out of range value for column 'b' at row 1

7. 整型的显示宽度和填零（zerofill）

//显示宽度最小为4位create table temp(i int(4) zerofill);//当不足四位，用0来填充四位insert into temp values(88);//当超过四位，就正常显示insert into temp values(123456);//最后结果mysql> select * from temp;+--------+| i      |+--------+|   0088 || 123456 |+--------+

浮点型

1. float和double类型

遵循IEEE 745浮点数表示

S：符号位E：指数位M：尾数位，也叫有效位数位N：(-1)^S × M × 2^E

- 单精度4个字节，32位，取值范围：-3.402823466E+38~3.402823466E
- 双精度8个字节，64位，取值范围：-1.7976931348623157E+308~1.7976931348623157E+308

float类型，精度6位。
double类型，精度17位。double有一个别名叫real
精度就是有效数字的个数，并不代表能表示的最大个数

2. 测试一下：

create table number(a float, b double);//插入一个数据insert into number values(123456789.123456789, 123456789.123456758888);//显示结果，float能够表示6位有效数字，double能表示17位，并且精度位四舍五入。+-----------+--------------------+| a         | b                  |+-----------+--------------------+| 123457000 | 123456789.12345676 |+-----------+--------------------+

3. 浮点数的显示宽度和填零（zerofill）

float（m， d）unsigned zerofillm表示总位数，d是小数点后的位数

//5位数，两位小数点的float类型，其表示范围为999.99 ～  -999.99create table teat(f float(5, 2));insert into teat values(-999.99);insert into teat values(999.99);//超出位数遵循四舍五入的原则mysql> insert into teat values(-999.999);ERROR 1264 (22003): Out of range value for column 'f' at row 1mysql> insert into teat values(-999.994);//插入成功Query OK, 1 row affected (0.13 sec)

4. decimal类型

decimal（m，d）unsigned zerofill1. m表示总位数，d是小数点后的位数，m和d可以缺省，默认分别是10和0。2. decimal**取值范围与double是一样的**，但是有**更高的精度**。3. numeric是decimal的别名

测试一下：

//最大30位，最多10个小数位，不足10位用0补足。 create table num(dec decimal(30, 10)); //插入数据并查看结果 insert into num values(123456789.123456789); mysql> select * from num;+----------------------+| d                    |+----------------------+| 123456789.1234567890 |+----------------------+1 row in set (0.00 sec)

日期和时间

• datetime类型

显示格式：YYYY-MM-DD HH:MM:SS取值范围：’1000-01-01 00:00:00’到’9999-12-31 23:59:59’空间大小：8字节例子：create table mytime(time datetime);insert into mytime values('2016-12-23 18:04:38');//插入一个正常值insert into mytime values('9999-12-31 23:59:59');//插入最大值insert into mytime values('0000-01-01 00:00:00');//插入一个比其范围小的年值也可以成功，但是MySQL并**不保证这类不在范围的值正常工作。insert into mytime values('99-01-01 00:00:00');//插入一个年值如果为两位数，则MySQL根据一下规则解释：- 70 ～ 99范围的年值转换为1979～1999；- 00 ～ 69范围的年值转换为2000～2069；mysql> insert into mytime values('10000-01-01 00:00:00');//插入大于其范围的值会出错ERROR 1292 (22007): Incorrect datetime value: '10000-01-01 00:00:00' for column 'time' at row 1mysql> select * from mytime;+---------------------+| time                |+---------------------+| 2016-12-23 18:04:38 || 9999-12-31 23:59:59 || 0000-01-01 00:00:00 || 1999-01-01 00:00:00 |+---------------------+

• timestamp类型

显示格式：YYYY-MM-DD HH:MM:SS取值范围：‘1970-01-01 00:00:00’到2038-01-19 03:14:07’空间大小：4字节例子：create table timestamp(t timestamp); insert into timestamp values('2038-01-19 03:14:07');//插入一个时间，由于存在时区问题，所以timestamp能表示的最大时间应该在加上8个小时 insert into timestamp values('2038-01-19 11:14:07');//插入范围上限的值，可以成功 mysql> insert into timestamp values('2038-01-19 11:14:08');//插入超出范围的值出错ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't' at row 1mysql> select * from timestamp;+---------------------+| t                   |+---------------------+| 2038-01-19 03:14:07 || 2038-01-19 11:14:07 |+---------------------+

• date类型

显示格式：YYYY-MM-DD 取值范围：’1000-01-01’到’9999-12-31’空间大小：3字节date类型和datetime类型相似，不存储时分秒，和datetime都有不保证小于其范围的值正常工作。

• time类型

显示格式：HH:MM:SS取值范围：‘-838:59:59’到’838:59:59’空间大小：3字节time类型能够取到负数范围，所以可以用来表示时间差。例子：insert into t_time values('-35:32:53');//插入一个负值insert into t_time values('1 00:32:53');//在小时前可以插入一个天数，它会换算成小时mysql> select * from t_time;+-----------+| t         |+-----------+| -35:32:53 || 24:32:53  |   //被换算成的time值+-----------+

• year类型

显示格式：YYYY取值范围：1901到2155空间大小：1字节year类型的数据在插入时可以以整数的形式插入，也可以以字符串类型插入，不能超过其范围。

字符串类型

• char和varchar类型

char(M)varchar(M)M表示的是字符数CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

值 char(4) 存储需求 varchar(4) 存储需求 “” ” 4字节 ” 1个字节 “ab” ‘ab’ 4字节 ‘ab’ 3个字节 “abcd” ‘abcd’ 4字节 ‘abcd’ 5个字节 “abcdefgh” ‘abcd’ 4字节 ‘abcd’ 5个字节

varchar存储值时，需要用一个字节来储存串的长度。

• binary和varbinary类型

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

• text类型

text分为四种1. tinytext     存储需求：L+1个字节，其中L < 2^82. text         存储需求：L+2个字节，其中L < 2^163. mediumtext   存储需求：L+3个字节，其中L < 2^244. longtext     存储需求：L+4个字节，其中L < 2^32VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，存储需要5个字节。对于CHAR、VARCHAR和TEXT类型，前面的表中的值L和M应解释为字符数目，并且列定义中的这些类型的长度表示字符数目。例如，要想保存一个TINYTEXT值需要L字符+ 1个字节。

• enum类型

//枚举类型只能插入枚举类型中含有的字符串create table t_enum(sex enum('male', 'female'));insert into t_enum values('male');insert into t_enum values('female');mysql> insert into t_enum values('other');//插入其他的字符串会戳错ERROR 1265 (01000): Data truncated for column 'sex' at row 1insert into t_enum values(1);//插入整型数字也可以，1与第一个字符串对应，实际上MySQL中保存的是整数。mysql> select sex+0 from t_enum;+-------+| sex+0 |+-------+|     1 ||     2 ||     1 |+-------+enum最多65535个值

• set类型

sei类型是按位存取的create table fruit_table(fruit set('apple', 'banana', 'orange'));//apple二进制代表1，banana二进制代表10，orange二进制代表100。insert into fruit_table values('apple,banana');//插入的字符串不能有空格，插入'apple,banana'相当与插入二进制11，十进制则为3insert into fruit_table values('apple,orange');//插入'apple,orange'相当于插入二进制101，十进制则为5mysql> select fruit+0 from fruit_table;+---------+| fruit+0 |+---------+|       3 | |       5 |+---------+