Mysql数据类型

1.数据类型分类

2.数值类型

 

tinyint类型 

带符号最大为127，最小为-128值，无符号最大为255值。若输入超过范围会被阻截，不能像C语言那样隐式类型转换。

有符号

mysql> create table tt1(num tinyint );

Query OK, 0 rows affected ( 0.02 sec)

mysql> insert into tt1 values ( 1 );

Query OK, 1 row affected ( 0.00 sec)

mysql> insert into tt1 values ( 128 ); -- 越界插入，报错

ERROR 1264 ( 22003 ): Out of range value for column 'num' at row 1

mysql> select * from tt1;

+------+

| num |

+------+

| 1 |

+------+

1 row in set ( 0.00 sec)

无符号

输入256大小被阻截。

 

3.bit类型

bit [(M)] : 位字段类型。 M 表示每个值的位数，范围从 1 到 64 。如果 M 被忽略，默认为 1 。

 

a的值没有出现是因为这是ASCII码值，这个位是不可显的。

mysql> create table tt4 ( id int , a bit ( 8 ));

Query OK, 0 rows affected ( 0.01 sec)

mysql> insert into tt4 values ( 10 , 10 );

Query OK, 1 row affected ( 0.01 sec)

mysql> select * from tt4; # 发现很怪异的现象， a 的数据 10 没有出现

+------+------+

| id | a |

+------+------+

| 10 | |

+------+------+

1 row in set ( 0.00 sec)

只能存储一位

4.小数类型

float

float [(m, d)] [ unsigned ] : M 指定显示长度， d 指定小数位数，占用空间 4 个字节

小数：float(4,2)表示显示4位，小数最多两位，范围是-99.99~99.99,MySQL在保存值会进行四舍五入。 

 

 定义为无符号类型，输入-0.1会阻截，因为只能是正数。

decimal

decimal (m, d) [ unsigned ] : 定点数 m 指定长度， d 表示小数点的位数

 decimal(5,2)表示的是范围是-999.99~999.99

decimal(5,2) unsigned 表示的范围是0~999.99

decimal与float很相似，区别在于精度，decima的进度高于float

下图可知，同样位数的decimal和float，float已经不准确了，而decimal是准确的，float精度大约是7位，decimal整数最大位数为65，支持小数最大位数d是30.d被省略，默认为0，m被省略默认为10.

 

5.字符串类型

char

char (L): 固定长度字符串， L 是可以存储的长度，单位为字符，最大长度值可以为 255

char(2)表示可以存放两个字符，可以是字母或汉字，但是不能超过两个，最多是255. 

vachar

 varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节，比如6只用1个那就是1个，上限是6，不用一定为6.

这里可以存放6个字符，汉字虽然是宽字符，但是这里不管字节数，而是字符数，字母和汉字都是字符。 

 varchar长度可以指定为0到65535之间的值，但有1-3字节用于记录数据的大小，这里1-3范围是因为数据大小不一样，要是数据大可能就3，小可能就1，有效数据是变化的，要是前面有其它的数据也会被影响。

表的编码是utf-8时，varchar(n)的参数n最大值为65535/3=21844，因为utf中，一个字符占用3个字节，如果编码是gbk，varchar(n)的参数n最大值是65535/2=32766(gbk中，一个字符占用2个字节)。

mysql> create table tt11(name varchar ( 21845 ))charset=utf8; -- 验证了 utf8 确实是不

能超过 21844

ERROR 1118 ( 42000 ): Row size too large. The maximum row size for the used

table type, not counting BLOBs, is 65535 . You have to change some columns to

TEXT or BLOBs

mysql> create table tt11(name varchar ( 21844 )) charset=utf8;

Query OK, 0 rows affected ( 0.01 sec)

char和varchar比较

 如果数据确定长度一样，就使用定长char，数据长度有变化就是用varchar，定长的磁盘空间比较浪费，但是效率高。

变长的磁盘空间比较节省，但是效率低。

6.日期和时间类型

date：日期' yyyy-mm-dd '，占用3个字节

datetime：时间日期格式 'yyyy-mm-dd:i i:s s '表示范围1000到9999，占用八个字节。

timestamp：时间戳，从1970年开始的 'yyyy-mm-dd:ii:ss'的格式和datetime完全一致，占用四个字节。

timestamp更新数据就会自动更新，显示当前时间。

 

7.enum和set

语法：

enum ：枚举， “ 单选 ” 类型；

enum(' 选项 1',' 选项 2',' 选项 3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“ 数字 ” ，因为这些选项的每个选项值依次对应如下数字： 1,2,3,.... 最多 65535个；当我们添加枚举值时，也可以添加对应的数字编号。 该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率 考虑，这些值实际存储的是“ 数字 ” ，因为这些选项的每个选项值依次对应如下数字： 1,2,4,8,16,32 ， .... 最多64 个。

可以使用数字访问，1 2 4 8是假设五位，那么第一个就是00001，第二个是00010，依此类推的，全部就是31。

查找数据

+-----------+---------------+--------+

| username | hobby | gender |

+-----------+---------------+--------+

| 雷锋 | 登山 , 武术 | 男 |

| Juse | 登山 , 武术 | 女 |

| LiLei | 登山 | 男 |

| LiLei | 篮球 | 男 |

| HanMeiMei | 游泳 | 女 |

+-----------+---------------+--------+

 使用一下查询

mysql> select * from votes where hobby= ' 登山 ' ;

+----------+--------+--------+

| username | hobby | gender |

+----------+--------+--------+

| LiLei | 登山 | 男 |

+----------+--------+--------+

缺点：只能查到只有登山的人，包含登山的查不到。

 

集合查询使用find_in_set函数 

find_in_set(sub,str_list):如果在sub在str_list中，则返回下标，不在返回0

mysql> select find_in_set( 'a' , 'a,b,c' );

+---------------------------+

| find_in_set( 'a' , 'a,b,c' ) |

+---------------------------+

| 1 |

+---------------------------+

mysql> select find_in_set( 'd' , 'a,b,c' );

+---------------------------+

| find_in_set( 'd' , 'a,b,c' ) |

+---------------------------+

| 0 |

+---------------------------+

mysql> select * from votes where find_in_set( ' 登山 ' , hobby);

+----------+---------------+--------+

| username | hobby | gender |

+----------+---------------+--------+

| 雷锋 | 登山 , 武术 | 男 |

| Juse | 登山 , 武术 | 女 |

| LiLei | 登山 | 男 |

+----------+---------------+--------+