Mysql数据类型_在mysql无符号数代码

和C语言一样，MYSQL中使用的SQL语句，也存在自身独有的类型，方便数据管理;

数值类型

tinyint类型

 create table tt1(num tinyint);#创建tinyint类型num
insert into tt1 values(1); -- 插入1
insert into tt1 values(128); -- 越界插入，报错 tinyint只有1个字节，范围-128~127;
select * from tt1;
1
2
3
4

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。

可以通过尾巴加UNSIGNED来说明某个字段是无符号的，类似于C语言;

create table tt2(num tinyint unsigned);#创建unsigned tinyint类型num
insert into tt2 values(-1); -- 报错，无符号，范围是： 0 - 255 c
1
2

其余整数类型signed or unsigned的范围自己推导;

bit类型

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

create table tt4 ( id int, a bit(8)); 
insert into tt4 values(10, 10);
select * from tt4; #发现很怪异的现象，a的数据->10没有出现
1
2
3

bit使用注意事项:

• bit字段在显示时，是按照ASCII码对应的值显示。

 insert into tt4 values(65, 65);
 select * from tt4;
1
2

• 如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间(只占用1位)

mysql> create table tt5(gender bit(1));

mysql> insert into tt5 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt5 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt5 values(2); -- 当插入2时，已经越界了
ERROR 1406 (22001): Data too long for column 'gender' at row 1
1
2
3
4
5
6
7
8
9
10

float类型

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

eg:小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt6 values(101, -99.991); #多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt6;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

同样，小数也有unsigned，如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99;(直接把负的去掉)

decimal类型

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

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

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

decimal和float很像，但是有区别:

• float和decimal表示的精度不一样

mysql> create table tt8 ( id int, salary float(10,8), salary2 decimal(10,8));

mysql> insert into tt8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt8;
+------+-------------+-------------+
| id | salary | salary2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 | -- 发现decimal的精度更准确，因此如果我们希望某个数据表示高精度，选择decimal

+------+-------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12

建议：如果希望小数的精度高，推荐使用decimal。

字符串类型

char类型

C里是字符类型，sql里是字符串类型;

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

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt9;
+------+--------+
| id | name |
+------+--------+
| 100 | ab |
| 101 | 中国 |
+------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

说明:

char(2) 表示可以存放两个字符（不是两字节！），可以是字母或汉字(取决于什么编码)，但是不能超过2个， 最多只能是255;

mysql> create table tt10(id int ,name char(256)); -- 报错，超过255个了
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or
TEXT instead
1
2
3

varchar类型

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节
1

mysql> create table tt10(id int ,name varchar(6)); --表示这里可以存放6个字符

mysql> insert into tt10 values(100, 'hello');

mysql> insert into tt10 values(100, '我爱你，中国');

mysql> select * from tt10;
+------+--------------------+
| id | name |
+------+--------------------+
| 100 | hello |
| 100 | 我爱你，中国 |
+------+--------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

说明:

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小（类似于C++ string底层的_size），所以说有效字节数是 65532。

• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字 节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

varchar长度是可变的，在不超过指定的len下，类似于C++的string，慢启动，用多少开多少，会自动扩容！

char和varchar比较

如何选择定长或变长字符串？(还是得从底层原理看待)

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。

• 定长(直接开自定义范围)的磁盘空间比较浪费，但是效率高。

• 变长(不超过自定义范围情况下，需要多少开多少)的磁盘空间比较节省，但是效率低。

日期和时间类型

常用的日期有如下三个：

• date :日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

-- 创建三种日期类型的表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据：
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); -- 插入两种时间，时间戳不用管
Query OK, 1 row affected (0.00 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2017-11-12 18:28:55 | -- 添加数据时，时间戳自动补上当前时间
+------------+---------------------+---------------------+

-- 更新数据：
mysql> update birthday set t1='2000-1-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2017-11-12 18:32:09 | -- 更新数据，时间戳会更新成当前时间
+------------+---------------------+---------------------+
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

enum和set类型

• enum(‘选项1’,‘选项2’,‘选项3’,…)：枚举，“单选”类型；
• set(‘选项值1’,‘选项值2’,‘选项值3’, …)：集合，“多选”类型；
• 凡是输入数据不在enum or set中给定的选项，就报错;–>约束用户输入，数据的确定性和一致性–>方便管理防止后续严重错误;

eg：有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

mysql> create table votes(
-> username varchar(30),
-> hobby set('登山','游泳','篮球','武术'), -- 注意：set有多种组合，使用数字标识每个爱好的时候，想想Linux权限，采用比特位位置来个set中的爱好组合起来
-> gender enum('男','女')); -- 注意：enum只能选一个，使用数字标识的时候，就是正常的数组的index (男，女)<->(1,2)
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5

插入数据:

insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);-- gender enum使用index 2 也能对应到女 --不建议 语义不明确
insert into votes values('wei',9,2);-- hobby enum使用类似于linux权限的二进制代表位置也能对应出选项组合 eg:(9 <二进制序列>1 0 0 1 中1的位置对应登山 和 武术)  --不建议 语义不明确

select * from votes where gender=2;
+----------+---------------+--------+
| username | hobby | gender |
+----------+---------------+--------+
| Juse | 登山,武术 |女 |
| Wei | 登山,武术 |女 |
+----------+---------------+--------+
1
2
3
4
5
6
7
8
9
10
11

有如下数据，想查找所有喜欢登山的人：

使用语句:

mysql> select * from votes where hobby='登山'; -- where...查不全
+----------+--------+--------+
| username | hobby | gender |
+----------+--------+--------+
| LiLei | 登山 | 男 |
+----------+--------+--------+
1
2
3
4
5
6

显然不能查询出所有，爱好为登山的人。

集合查询使用**find_ in_ set(‘x’,‘xyz’)**函数：

mysql> select * from votes where find_in_set('登山', hobby);-- 专用的find_ in_ set(x,x的集合名)查的全

+----------+---------------+--------+
| username | hobby | gender |
+----------+---------------+--------+
| 雷锋 | 登山,武术 | 男 |
| Juse | 登山,武术 | 女 |
| LiLei | 登山 | 男 |
+----------+---------------+--------+
1
2
3
4
5
6
7
8
9

数据的确定性和一致性

为什么越界会报错，截断不行吗？

mysql是一个专门用来管理数据的软件，要非常严谨，保证数据具有确定性和一致性，包括长度等，这就要求使用者必须按照要求，比如身份证长度总不可能100位等等，其实类型本质上也是一种为了更好管理使用者录入数据而规定的的约束;

为什么MYSQL要有数据类型？