1、MySQL 数据类型
1.1 MySQL 数据类型介绍
在MySQL数据库管理系统中,可以通过存储引擎来决定表的类型。
同时,MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。
数据类型是数据的一种属性,其可以决定数据的存储格式、有效范围和相应的限制。MySQL的数据类型包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型和二进制数据类型。
- 整数类型、浮点数类型和定点数类型
- 日期与时间类型
- 字符串类型
- 二进制类型
整数类型
作用:用于存储用户的年龄、游戏的Level、经验值等。
分类:tinyint smallint mediumint int bigint
常用的是int
显示宽度:类型后面小括号内的数字是显示的最小宽度,并不能限制插入数值的大小
比如:bigint(2) 2是显示宽度
结论:
- 当整数值超过 int 数据类型支持的范围时,就可以采用 bigint。
- 在 MySQL 中,int 数据类型是主要的整数数据类型。
- int(n)里的n是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度
#整形的宽度仅为显示宽度,不是限制。因此建议整形无须指定宽度。
示例
mysql> create table qf.test1(age int(10)); //在qf库下创建test1表格,设置数据类型
mysql> insert into test1(age) values(1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(age) values(2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(age) values(2147483648); //超出范围,报错
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> select * from test1; //查询
+------------+
| age |
+------------+
| 100 |
| 1000 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)
浮点数类型(小数点类型)
作用:用于存储用户的身高、体重、薪水等
分类
浮点类型:
单精度浮点类型:float(m,d) m最大取值255,d最大取值7 双精度浮点类型:double(m,d) m最大取值255,d最大取值14 定点数类型: DEC等同decimal(m,d) m最大取值65,d最大取值30 注意:定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等 精度高的数据。
语法:
mysql> create table test4(float_test float(5,2)); ##宽度不算小数点,“(5,2)”的意思是不超过5位,小数
占3位.做了限制
示例:
mysql> create table test4(float_test float(5,2)); #案例
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test4(float_test) values(123.345534354);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test4(float_test) values(34.39567);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test4(float_test) values(678.99993);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test4(float_test) values(6784.9);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
mysql> select * from test4;
+------------+
| float_test |
+------------+
| 123.35 |
| 34.40 |
| 679.00 |
+------------+
4 rows in set (0.00 sec)
字符串类型
作用:用于存储用户的姓名、爱好、电话,邮箱地址,发布的文章等
分类: char 、 varchar
char表示定长字符串,长度是固定的;如果插入数据的长度小于char的固定长度时,则用空格填充;
优点:因为长度固定,所以存取速度要比varchar快很多,甚至能快50%,
缺点:但正因为其长度固定,所以会占据多余的空间。varchar表示可变长字符串,长度是可变的;插入的数据是多长,就按照多长来存储;
缺点: varchar在存取方面与char相比存取速度慢
优点: 因为长度不固定,所以不占据多余的空间。结合性能角度(char更快),节省磁盘空间角度(varchar更小),具体情况还需具体来设计数据库才是妥当的做法。
- char(10) 设置了10个,实际占用就是10个长度
列的长度固定为创建表时声明的长度: 0 ~ 255- varchar(10) 设置了10个,实际用了几个就占几个
列中的值为可变长字符串,长度: 0 ~ 65535
示例:
mysql> create table t8(c char(5),v varchar(12));
Query OK, 0 rows affected (0.42 sec)
mysql> insert into t8 values('abcde','abcdef');
Query OK, 1 row affected (0.38 sec)
mysql> insert into t8 values('abc','abcdef'); #char可以少于规定长度。
Query OK, 1 row affected (0.05 sec)
mysql> insert into t8 values('abc777','abcdef7'); #char不能大于规定的长度。
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql>
总结:
1.经常变化的字段用varchar
2.知道固定长度的用char
3.超过255字符的只能用varchar或者text
4.能用varchar的地方不用text
text:文本格式
设置字符集(使能够插入汉字)
- 想插入汉字,需要设置字符集为utf8
第一种:
修改配置文件,增加如下配置
character_set_server = utf8
注:在修改配置文件之前创建的库中的表,不能使用汉字
第二种:
创建表的时候指定字符集:
create table t6(name varchar(20)) charset=utf8;
insert into t6 values('字符集');
- 查看字符集
查看表的字符集:
mysql> show create table t6;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------+
| t6 | CREATE TABLE `t6` (
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+
查看库的字符集:
mysql> show create database qf;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| qf | CREATE DATABASE `qf` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
字符串类型总结
总结:
1.经常变化的字段用varchar
2.知道固定长度的用char
3.超过255字符的只能用varchar或者text
4.能用varchar的地方不用text
text:文本格式
枚举类型
语法:
在创建表时设置枚举类型
create table 表名(字段 enum('枚举类型设置的内容','枚举类型设置的内容'))
设置后只能从 设置的枚举类型的内容 中选择。
案例:
mysql> create table t10(name enum('m','w'));
只能从tom,jim两个里面2选其1
mysql> insert into t10(sex) values('m');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values('w');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values('n');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
日期类型
时间和日期类型: year、date、time、datetime、timestamp
作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等
写入年份时 分两种 :
- 插入年份时,尽量使用4位值
- 插入两位年份时,<=69,以20开头,比如65, 结果2065
>=70,以19开头,比如82,结果1982
案例:
mysql> create table test_time(d date,t time,dt datetime);
Query OK, 0 rows affected (0.03 sec)+
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-08-23 | 00:26:29 | 2019-08-23 00:26:29 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
测试年:
mysql> create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t3;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
+-----------+
2 rows in set (0.00 sec)
mysql> insert into t3 values (2019),(81);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
| 2019 |
| 1981 |
+-----------+
4 rows in set (0.00 sec)
mysql>