数据类型
数据类型
数据类型是定义列中可以存储什么数据以及 该数据怎样存储的基本规则在设计表时，应该特别重视所用的数据类型。使用错误的数据类型，可能会严重地影响应用程序的功能和性能。更改包含数据的列不是一件小事（数据还有可能丢失）
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字节 |
浙公网安备 33010602011771号