数据类型

数据类型

数据类型是定义列中可以存储什么数据以及 该数据怎样存储的基本规则在设计表时，应该特别重视所用的数据类型。使用错误的数据类型，可能会严重地影响应用程序的功能和性能。更改包含数据的列不是一件小事（数据还有可能丢失）

1字符串数据类型

最常用的数据类型是串数据类型

数据类型	说明
CHAR	1-255个字符的定长串，它的长度必须在创建时指定，否则mysql假定为CHAR(1)
ENUM	接受最多64个串组成的一个预定义集合的某个串
LONGTEXT	与TEXT相同，但最大长度为4GB
MEDIUMTEXT	与TEXT相同，但最大长度为16K
SET	接受最多64个串组成的一个预定义集合的零个串或多个串
TEXT	最大长度为64K的边长文本
TINYTEXT	与TEXT相同，最大长度为255字节
VARCHAR	长度可变，最多不超过255字节。如果在创建时指定为varchar(n).则可以存储0到n个字符的边长串（n<=255)

有两种基本的串类型，分别为定长串和变长串

定长串接受长度固定的字符串，其长度是在创建表时指定的。
定长串不允许多余指定的字符数目，它们分配的存储空间和指定的一样多
CHAR属于定长串

变长串存储可变长的文本，有些变长数据类型具有最大的定长。而有些是完全变长的
TEXT,VARCHAR属于变长型

从资源节约角度来看,用的VARCHAR较多,用多少申请多少,而不是最开始指定

char(5)是限制长度为5个字符

mysql> create table t8(c char(5), v varchar(5));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t8 values('qwerd','qwerdf');
ERROR 1406 (22001): Data too long for column 'v' at row 1
mysql> insert into t8 values('qwerdf','qwerd');
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> insert into t8 values('qwerd','qwerd');
Query OK, 1 row affected (0.02 sec)

在检索时,char列会删除尾部的空格,varchar会保留空格

mysql> insert into t8 values('q     ','q     ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select length(c),length(v) from t8;
+-----------+-----------+
| length(c) | length(v) |
+-----------+-----------+
|         5 |         5 |
|         1 |         5 |
+-----------+-----------+
2 rows in set (0.00 sec)

不管使用何种形式的串数据类型，都必须阔在引号内（通常单引号更好）
电话号码和邮政编码应该存储在串数据类型中。（如果存储在数值字段中开头为0，实际上会丢失一位数字）

2数值数据类型

数据类型	说明
BIT	位字段，1~64位
BIGINT	整数型，支持-9223372036854775808~+9223372036854775807 （如果是UNSIGNED，为0~18446744073709551615）的数
BOOLEAN（或BOOL）	布尔，或者为0或者为1，主要用于on/off
DECIMAL	（或DEC
DOUBLE	双精度浮点型
FLOAT	单精度浮点型
INT（或INTEFER）	整数型，支持-2147483648+2147483647（如果是UNSIGNED，为04294967295）的数
MEDIUMINT	整数型，支持-83886088388607（如果是UNSIGNED，为016777215）的数
REAL	4字节的浮点值
SMALLINT	整数值，支持-3276835767（如果是UNSIGNED，为065535）的数
TINYINT	整数型，支持-128到127（如果是UBSIGNED），为（0~255）的数

数值不应该括在引号内
除BIT和BOOLEAN外，其余所有数值数据类型都可以有符号或无符号
没有专门存储货币的数据类型，一般情况下使用DECIMAL（8，2）

常用数值数据类型

• int(常规整形)
• tinyint(最小整形)

mysql> create table test1(tinyint_test tinyint,int_test int);
Query OK, 0 rows affected (0.21 sec)

mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES  |     | NULL    |       |
| int_test     | int(11)    | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> 

插入数据(dml)

mysql> insert into test1 values('111','111');
Query OK, 1 row affected (0.03 sec)

查看

mysql> select * from test1;
+--------------+----------+
| tinyint_test | int_test |
+--------------+----------+
|          111 |      111 |
+--------------+----------+
1 row in set (0.00 sec)

mysql> 

指定行插入数据(超出范围插入失败)

mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

mysql> insert into test1(tinyint_test) values(127);
Query OK, 1 row affected (0.02 sec)

mysql> 

在此基础上添加无符号约束

mysql> create table test2(tinyint_test tinyint unsigned,int_test int unsigned);
Query OK, 0 rows affected (0.19 sec)

发现添加unsigned后发现,tinyint数值类型可以支持到255

mysql> insert into test2(tinyint_test) value(255);
Query OK, 1 row affected (0.03 sec)

但是添加unsigned后不支持输入负数

mysql> insert into test2(tinyint_test) value(-1);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

mysql> insert into test1(tinyint_test) value(-1);
Query OK, 1 row affected (0.03 sec)

设置宽度

mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school;
Database changed

mysql> create table ti(id1 int,id2 int(8));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into ti values(123456789,123456789);
Query OK, 1 row affected (0.04 sec)

mysql> select * from ti;
+-----------+-----------+
| id1       | id2       |
+-----------+-----------+
| 123456789 | 123456789 |
+-----------+-----------+
1 row in set (0.00 sec)

再次添加限制zerofill用零填充

mysql> create table t2(id1 int zerofill,id2 int(8) zerofill);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t2 values(123456789,123456789);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t2 values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------------+-----------+
| id1        | id2       |
+------------+-----------+
| 0123456789 | 123456789 |
| 0000000001 |  00000001 |
+------------+-----------+
2 rows in set (0.00 sec)

整形的宽度仅为显示宽度,不是限制,因此建议整形无须指定宽度

浮点型

用于存储用户的身高,体重,薪水等

float(5,2)标示一共5位,小数占2位

 mysql> create table t3(float_test float(5,2));
Query OK, 0 rows affected (0.18 sec)

 mysql> insert into t3 values(10.12);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t3 values(219.123);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
+------------+
| float_test |
+------------+
|      10.12 |
|     219.12 |
+------------+
2 rows in set (0.00 sec)

定点型

mysql> create table t4(decimal_test decimal(5,2));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t4 values(70.245);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into t4 values(70.244);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+--------------+
| decimal_test |
+--------------+
|        70.25 |
|        70.24 |
+--------------+
2 rows in set (0.00 sec)

日期和时间数据类型

数据类型	说明
DATE	表示日期，格式为YYYY-MM-DD
TIME	格式为HH：MM：ss
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同，但范围较小
YEAR	用2位数字表示，范围是70（1970）-69（2069）；用4位数字表示，范围是1901~2155

mysql> create table t5(d date, t time, dt datetime);
Query OK, 0 rows affected (0.20 sec)

now()函数返回当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-10-08 20:41:20 |
+---------------------+
1 row in set (0.00 sec)

插入当前时间

mysql> insert into t5 values(now(), now(), now());
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t5;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-10-08 | 20:43:10 | 2019-10-08 20:43:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

时间戳

mysql> create table t6(id timestamp);
Query OK, 0 rows affected (0.21 sec)

mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01 sec)

默认是当前时间,插入null值时,会插入当前时间

mysql> insert into t6 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+---------------------+
| id                  |
+---------------------+
| 2019-10-08 20:51:30 |
+---------------------+
1 row in set (0.00 sec)

注意事项

• 插入年份时,尽量使用4位值
• 插入两位时,
  • <=69,以20开头,如2052

  • =70,以19开头,如1982

mysql> create table t7(y year);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t7 values(69);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t7 values(70);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t7 values(2019);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t7;
+------+
| y    |
+------+
| 2069 |
| 1970 |
| 2019 |
+------+
3 rows in set (0.00 sec)

mysql> 

枚举和集合

• enum 枚举.只能在给定的范围内选一个值,如男/女
• set 集合,在给定的范围内选择一个或一个以上的值

新建数据表

mysql> create table student1(name varchar(50), sex enum('m','f'), hobby set('book','music','disc','game'));
Query OK, 0 rows affected (0.19 sec)

mysql> desc student1;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('m','f')                     | YES  |     | NULL    |       |
| hobby | set('book','music','disc','game') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入值

mysql> insert into student1 values('tom','m','disc,game');
Query OK, 1 row affected (0.03 sec)

mysql> select * from student1;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | m    | disc,game |
+------+------+-----------+
1 row in set (0.00 sec)

二进制数据类型

二进制数据类型可以存储任何类型，如图像，多媒体，字处理文档等

数据类型	说明
BLOB	blob最大长度位64KB
MEDIUMBLOB	blob最大长度位16MB
LONGBLOB	blob最大长度位4GB
TINYBLOB	blob最大长度位255字节