与C/C++等编程语言不同的是,数据库对数据类型的要求非常严格,数据库需要严格把控数据的准确性
数据类型
数值类型
TINYINT类型
mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.01 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)
- 在MySQL中,可以指定整型有无符号,默认是有符号的
- 可以通过UNSIGNED来说明某个字段无符号
案例
mysql> create table tt2(num tinyint unsigned);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tt2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tt2 values(255);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt2;
+------+
| num |
+------+
| 255 |
+------+
1 row in set (0.00 sec)
尽量不要使用unsigned,不仅是因为这会在存储负数时产生错误,而且一般对于int类型存放不下的类型,int unsigned也很可能无法存下,因此在设计时直接将int类型提升为bigint会更好
bit类型
bit[(M)]:位字段类型,M表示每个值的位数,范围从1到64,如果M被忽略,默认为1
案例
mysql> create table tt4 (id int, a bit(8));
Query OK, 0 rows affected (0.04 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.01 sec)
bit使用的注意事项:
- bit字段在显示时,是按照ASCII码对应的值显示
mysql> insert into tt4 values(65,65);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt4;
+------+------+
| id | a |
+------+------+
| 10 | |
| 65 | A |
+------+------+
2 rows in set (0.00 sec)
- 如果我们有这样的值,只存放0或1,这时可以定义bit(1)。这样可以节省空间
ysql> create table tt5(gender bit(1));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into tt5 values(0);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt5 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt5 values(2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1
小数类型
float
float[(m,d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节
案例
float(4,2)表示的范围是-99.99~99.99,MySQL在保存值时会四舍五入
mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tt6 values(100,-99.99);
Query OK, 1 row affected (0.06 sec)
mysql> insert into tt6 values(101,-99.991);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt6 values(102,-99.994);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt6 values(103,-99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from tt6;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
| 102 | -99.99 |
+------+--------+
3 rows in set (0.00 sec)
float unsigned
如果定义的是float(4,2) unsigned,范围是0~99.99
mysql> create table tt7(id int, salary float(4,2) unsigned);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into tt7 values(100, -0.1);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------+
| Error | 1264 | Out of range value for column 'salary' at row 1 |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tt7 values(101, -0);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt7 values(102, 99.99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt7;
+------+--------+
| id | salary |
+------+--------+
| 101 | 0.00 |
| 102 | 99.99 |
+------+--------+
2 rows in set (0.00 sec)
decimal
decimal(m,d) [unsigned] : 定点数m指定长度,d表示小数点的位置
- decimal(5,2)表示的范围是-999.99~999.99
- decimal(5,2) unsigned表示的范围是0~999.99
- decimal和float的区别:float和decimal表示的精度不同
mysql> create table tt8 (id int,salary1 float(10,8),salary2 decimal(10,8));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tt8 values(100,23.12345612,23.12345612);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt8;
+------+-------------+-------------+
| id | salary1 | salary2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 |
+------+-------------+-------------+
1 row in set (0.00 sec)
- float表示的精度大约是7位
- decimal整数最大位数为65。支持小数最大位数d是30。如果m被省略,默认是10,如果d被省略,默认为0。
- 因此如果希望小数的精度高,就推荐使用decimal
字符串类型
char
char(L):固定长度字符串,L是可以存储的长度,单位为字符,最大长度可以为255
案例
mysql> insert into tt9 values(100,'ab');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt9 values(101,'甲乙');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt9 values(102,'甲乙丙');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into tt9 values(103,'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from tt9;
+------+--------+
| id | name |
+------+--------+
| 100 | ab |
| 101 | 甲乙 |
+------+--------+
2 rows in set (0.00 sec)
varchar
varchar(L):可变长度字符串,L表示字符长度,最大长度65535个字节
案例
mysql> create table tt10(id int, name varchar(6));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tt10 values(100, 'hello');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt10 values(101, '我爱你,中国');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt10;
+------+--------------------+
| id | name |
+------+--------------------+
| 100 | hello |
| 101 | 我爱你,中国 |
+------+--------------------+
2 rows in set (0.00 sec)
varchar(len),len到底是多大,和表的编码密切相关:
- varchar长度可以指定为0到65535之间的值,但是有1-3个字节用于记录数据大小,所以说有效字节数是65532.
- 当表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844(因为utf中,每个中文字符占用3个字节),如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占2字节)。
实际存储 | char(4) | varchar(4) | char占用字节 | varchar占用字节 |
---|---|---|---|---|
abcd | abcd | abcd | 4*3=12 | 4*3+1=13 |
A | A | A | 4*3=12 | 1*3+1=4 |
abcde | 无 | 无 | 数据超过长度 | 数据超过长度 |
如何选择定长或变长字符串
- 如果数据长度确定,就使用定长(char),比如:身份证,手机号,md5
- 如果数据长度有变化,就是用变长(varchar),比如:名字,地址,但是要保证最长的能存进去
- 定长的比较浪费磁盘空间,但是效率高
- 变长的比较节省磁盘空间,但是效率低
日期和时间类型
常用的日期类型有如下三个
date
:日期yyyy-mm-dd
,占用三字节datetime
:时间日期格式yyyy-mm-dd HH:ii:ss
占用八字节timestamp
:时间戳,从1970年开始的yyyy-mm-dd HH:ii:ss
格式和datetime完全一致,占用四字节
案例:
mysql> create table `time` (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| student |
| time |
| tt10 |
+-------------------+
3 rows in set (0.00 sec)
mysql> insert into `time`(t1,t2) values('1999-9-9', '2008-8-8 12:1:1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from `time`;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1999-09-09 | 2008-08-08 12:01:01 | 2022-10-10 10:11:29 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
//更新数据
mysql> update `time` set t1='2000-1-1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from `time`;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2022-10-10 10:13:30 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
String类型
语法:
- enum:枚举,“单选类型”
enum('选项1','选项2','选项3',...);
该设定只是提供若干选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,这些值实际存储的是“数字”,这些选项的每个选项值依次对应如下数字:1,2,3,…65535;在添加枚举值时,也可以添加对应的数字编号
- set:集合,“多选”类型
set('选项一','选项2','选项三',...);
该设定只是提供了若干选项的值,一个单元格中,设计可存储其中任意多个值;而且出于效率考虑,这些值实际存储的是“数字”,这些选项的每个选项值对应如下数字:1,2,4,8,16,32…最多64个。
不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。
案例:
有一个调查表votes,需要调查人的喜好(可以多选),性别(单选)
mysql> create table votes(name varchar(30), hobby set('登山', '游泳', '篮球', '网球'), gender enum('男', '女'));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into votes values('张三', '登山,网球', '男');
Query OK, 1 row affected (0.01 sec)
mysql> insert into votes values('jack', '登山,网球', 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from votes;
+--------+---------------+--------+
| name | hobby | gender |
+--------+---------------+--------+
| 张三 | 登山,网球 | 男 |
| jack | 登山,网球 | 女 |
+--------+---------------+--------+
2 rows in set (0.00 sec)
mysql> select * from votes where gender=2;
+------+---------------+--------+
| name | hobby | gender |
+------+---------------+--------+
| jack | 登山,网球 | 女 |
+------+---------------+--------+
1 row in set (0.00 sec)
现有如下数据,想要查找所有喜欢登山的人
+--------+---------------+--------+
| name | hobby | gender |
+--------+---------------+--------+
| 张三 | 登山,网球 | 男 |
| jack | 登山,网球 | 女 |
| 李四 | 登山 | 男 |
| rose | 篮球 | 女 |
| 王五 | 游泳 | 男 |
+--------+---------------+--------+
使用如下语句:
mysql> select * from votes where hobby='登山';
+--------+--------+--------+
| name | hobby | gender |
+--------+--------+--------+
| 李四 | 登山 | 男 |
+--------+--------+--------+
1 row in set (0.00 sec)
只能查询只喜欢登山的人
集合查询使用find_in_set函数
find_in_set(sub,str_list)
:如果sub
在str_list
中,则返回下标,如果不再,返回0;str_list用逗号分隔字符串。
mysql> select find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select find_in_set('d','a,b,c');
+--------------------------+
| find_in_set('d','a,b,c') |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
查询爱好登山的人:
mysql> select * from votes where find_in_set('登山',hobby);
+--------+---------------+--------+
| name | hobby | gender |
+--------+---------------+--------+
| 张三 | 登山,网球 | 男 |
| jack | 登山,网球 | 女 |
| 李四 | 登山 | 男 |
+--------+---------------+--------+
3 rows in set (0.00 sec)