Mysql数据类型

最新推荐文章于 2024-09-12 01:15:37 发布

沐、小原

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量154

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_45313989/article/details/103695569

版权

一.MySQL的数据类型
1.数据类型概述
数据类型是数据的一种属性，其可以决定数据的存储格式，有效范围和相应的限制。mysql的数据类型包括整数类型，浮点数类型，日期和时间类型，字符串类型和二进制类型

2.为什么定义数据类型?为什么要数据分类?
(1) 使系统能够根据数据类型来操作数据。
(2) 预防数据运算时出错。
(3) 更有效的利用空间。数据分类，可以使用最少的存储来存放数据，同时提高性能。

3.数据类型解释
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

4.数值类型
(1)数值
在这里插入图片描述
(2)日期和时间类型

(3)字符串类型

在MySQL中支持的5个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。这些类型在很大程度是相同的，只有他们存储的值的大小是不同的。

5.使用举例
(1)如果用来保存用户的年龄 (举例来说，数据库中保存年龄是不可取的)，用 TINYINT 就够了;

(2)如果要用作一个肯定不会超过 16000000 行的表的 AUTO_INCREMENT 的 IDENTIFY 字段，当然用 MEDIUMINT 不用 INT，试想，每行节约一个字节，16000000 行可以节约 10 兆多。

(3) 注意：再MySQL5.7中，插入不符合数据类型的数据字段，服务被报错，不允许插入

二.实验部分
1.数据类型的测试

mysql> create database jeff;  ###创建数据库 
Query OK, 1 row affected (0.00 sec)

mysql> use jeff;   ###进入数据库
Database changed
mysql> create table kdata(fti tinyint,fsi smallint,fmi mediumint,fi int,fbi bigint);  ##创建表
Query OK, 0 rows affected (0.13 sec)

mysql> insert into kdata values(123456789,123456789,123456789,123456789,123456789);
ERROR 1264 (22003): Out of range value for column 'fti' at row 1
 ###错误1264（22003）：第1行“fti”列的值超出范围，对于不符合的数据报错
 mysql> insert into kdata values(123,12345,1234567,123456789,123456789);
Query OK, 1 row affected (0.00 sec)  创建成功
mysql> select * from kdata;  查看kdata表的结构
+------+-------+---------+-----------+-----------+
| fti  | fsi   | fmi     | fi        | fbi       |
+------+-------+---------+-----------+-----------+
|  123 | 12345 | 1234567 | 123456789 | 123456789 |
+------+-------+---------+-----------+-----------+
1 row in set (0.00 sec)

2.int类型修饰符
（1）unsigned 无符号整数，修饰符：规定字段只能保存正的数据。它可以增大这个字段的正数支持的范围。
（2）zerofill 修饰符：规定0（不是空格）填补输出的值。使用这个值可以防止 mysql存储负值。
(3)unsgined和zerofill使用方法

mysql> create table kdata2(fi int,fiu int unsigned,fiz int zerofill,fiuz int unsigned zerofill);
Query OK, 0 rows affected (0.02 sec)

(4)查看表结构

mysql> desc kdata2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| fi    | int(11)                   | YES  |     | NULL    |       |
| fiu   | int(10) unsigned          | YES  |     | NULL    |       |
| fiz   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| fiuz  | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
### 注意：发现fiz和fiuz字段值是一样的。

###查看原因：
mysql> show create table kdata2;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                     |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| kdata2 | CREATE TABLE `kdata2` (
  `fi` int(11) DEFAULT NULL,
  `fiu` int(10) unsigned DEFAULT NULL,
  `fiz` int(10) unsigned zerofill DEFAULT NULL,
  `fiuz` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

###测试：
mysql> insert into kdata2 values(10,10,10,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into kdata2 values(-10,-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'fiu' at row 1
Query OK, 1 row affected (0.00 sec)
mysql> select  * from kdata2;
+------+------+------------+------------+
| fi   | fiu  | fiz        | fiuz       |
+------+------+------------+------------+
|  -10 |   10 | 0000000100 | 0000001000 |
+------+------+------------+------------+
1 row in set (0.00 sec)
### 可以看见在fiu,fiz和fiuz中存储负数，直接报错，因为unsgined和zerofill不允许使用负值，zerofill会自动添加unsgined，并且在数值前面补0

###
mysql> create table azerofill(fi int(3),fiz int(3) zerofill,fiuz int(4) unsigned zerofill);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into azerofill values(11,11,11);
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> select * from azerofill;
+------+------+------+
| fi   | fiz  | fiuz |
+------+------+------+
|   11 |  011 | 0011 |
+------+------+------+
1 row in set (0.00 sec)

注意:
int(M) 在 integer 数据类型中，M 表示最大显示宽度。
在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。其实，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
如int的值为10
int（10）显示结果为0000000010
int（3）显示结果为010
就是显示的长度不一样而已都是占用四个字节的空间，可以使用的空间也一样。当我们产成固定长度的序列号时，可以使用zerofill。

(5)测试，插入大超过显示范围的值。

mysql> insert into azerofill values(123456,123456,123456);
Query OK, 1 row affected (0.00 sec)
mysql> select  * from azerofill;
+--------+--------+--------+
| fi     | fiz    | fiuz   |
+--------+--------+--------+
|     11 |    011 |   0011 |
| 123456 | 123456 | 123456 |
+--------+--------+--------+
2 rows in set (0.00 sec)
 ###发现位数不够用0补齐，超过设定值，正常显示。

3.浮点型数据类型
(1)小数点后超过1位四舍五入
float(3,1) ：表示此字段有效位数为3位，小数点后面1位数字。小数点后超过1位，mysql自动给四舍五入。

mysql> create table kdata3(tf float(3,1)); ###创建kdata3表
Query OK, 0 rows affected (0.36 sec)

mysql> insert into kdata3 values(23.45);  ###插入记录
Query OK, 1 row affected (0.30 sec)

mysql> select * from kdata3;
+------+
| tf   |
+------+
| 23.4 |
+------+
1 row in set (0.00 sec)

发现整数+小数一共只能有3位，整数2位，小数1位，小数点后超过1位自动四舍五入。

(2)存储精确的小数
double ：8个字节来存储
decimal: 用来存储精确的小数，消耗空间，运算慢，一般想办法使用bigint替代它。
decimal: double要比float存储小数更精确。

mysql> create table kdata4(tf float(5,2),td double(5,2),tdc decimal(5,2));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into kdata4 values(56.346,56.346,56.346);
Query OK, 1 row affected, 1 warning (0.30 sec)

mysql> select * from kdata4;
+-------+-------+-------+
| tf    | td    | tdc   |
+-------+-------+-------+
| 56.35 | 56.35 | 56.35 |
+-------+-------+-------+
1 row in set (0.00 sec)

###对比
mysql> create table kdata5(tf float(10,2),td double(10,2),tdc decimal(10,2));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into kdata5 values(1234567.89,1234567.89,1234567.89);
Query OK, 1 row affected (0.29 sec)

mysql> select * from kdata5;
+------------+------------+------------+
| tf         | td         | tdc        |
+------------+------------+------------+
| 1234567.88 | 1234567.89 | 1234567.89 |
+------------+------------+------------+
1 row in set (0.00 sec)

这里丢失数据的原因是因为单精度浮点数的有效数字位位8位，而我们定义的10位，数据也是10位，所以最后就造成数据丢失了。

（3)单精度浮点和双精度浮点
单精度浮点数在机内占4个字节，用32位二进制描述。
j双精度浮点数在机内占8个字节，用64位二进制描述

4.字符串类型
(1)char
char ：后面括号中必须有数值，来确认字符串的范围。大小范围：0-255.
char(10) ; 指定了一个长度为10的字符值。
旧版本小于长度，空格自动补齐，大于长度，自动截短。
新版本超过长度自动报错。

mysql> create table kdata6(aaa char(10));  ###创建表
Query OK, 0 rows affected (0.05 sec)

mysql> insert into kdata6 values('1234567890111'); ###超出数值范围
ERROR 1406 (22001): Data too long for column 'aaa' at row 1

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

mysql> select * from kdata6;
+------------+
| aaa        |
+------------+
| 1234567890 |
+------------+
1 row in set (0.00 sec)

（2）binary 修饰符: 区分字符大小写

mysql> alter table kdata6 modify aaa char(10) binary;  ###区分字符
Query OK, 2 rows affected (0.21 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into kdata6 values('ABC');
Query OK, 1 row affected (0.05 sec)

mysql> select * from kdata6 where aaa='abc';
+------+
| aaa  |
+------+
| abc  |
+------+
1 row in set (0.07 sec)

mysql> select * from kdata6 where aaa='ABC';
+------+
| aaa  |
+------+
| ABC  |
+------+
1 row in set (0.00 sec)

（3）varchar
在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节的数据，起始位和结束位占去了3个字节。

varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。

mysql> create table kdata7(aaa varchar(4));  ###创建表
Query OK, 0 rows affected (0.06 sec) 

mysql> insert into kdata7 values("abc"); ###插入记录
Query OK, 1 row affected (0.00 sec)

mysql> insert into kdata7 values("abcdefg");  ### 超过长度自动报错
ERROR 1406 (22001): Data too long for column 'aaa' at row 1

（4）char和varchar区别
CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度。

从空间上考虑，用varchar合适；从效率上考虑，用char合适。

5.日期和时间类型
（1）date 日期

mysql> create table kdata8(birthday date); ###创建表
Query OK, 0 rows affected (0.07 sec)

mysql> insert into kdata8 values('2019-12-27'),(20201227); ###插入记录
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from kdata8;
+------------+
| birthday   |
+------------+
| 2019-12-27 |
| 2019-12-27 |
| 2019-12-27 |
| 2020-12-27 |
+------------+       ###显示方式一样
4 rows in set (0.00 sec)

（2）time时间

mysql> create table kdata9(showtime time); ###创建表
Query OK, 0 rows affected (0.06 sec)

mysql> insert into kdata9 values('11:10:23'),('11:23'),('112456'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from kdata9;
+----------+
| showtime |
+----------+
| 11:10:23 |
| 11:23:00 |
| 11:24:56 |
+----------+
3 rows in set (0.00 sec)

（3）Year
year ： 00-69自动转为： 2000-2069 , 70-99自动转为1970-1999

mysql> create table kdata10(test year);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into kdata10 values(2019),(09),(1),(34),(99);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from kdata10;
+------+
| test |
+------+
| 2019 |
| 2009 |
| 2001 |
| 2034 |
| 1999 |
+------+
5 rows in set (0.00 sec)

（4）datatime 或 timestamp
datetime类型能保存的最大范围的值为1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYY-MM-DD HH-MM-SS的整数中，与时区无关，使用8个字节的存储空间。

timestamp类型保存了从1970年1月1日（格林尼治时间）以来的秒数，它和linux的时间戳相同，只是用了4个字节的存储空间，因此它的范围比datetime的范围小了很多，只能表示从1970年到2038年，我们可以使用它提高空间利用率。

mysql> create table kdata11(f_datatime datetime,f_timestamp timestamp);
Query OK, 0 rows affected (0.63 sec)

mysql> insert into kdata11 values('1999-11-12 23:23:45','19991112232345');
Query OK, 1 row affected (0.11 sec)

mysql> select * from kdata11;
+---------------------+---------------------+
| f_datatime          | f_timestamp         |
+---------------------+---------------------+
| 1999-11-12 23:23:45 | 1999-11-12 23:23:45 |
+---------------------+---------------------+
1 row in set (0.03 sec)

### 如果为空，直接报错
mysql> insert into kdata11 values(now(),null());
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '())' at line 1

6.复合类型
它们字段的值，必须从预先定义好的字符串集合中选取。 ENUM（枚举）：只能取一个，用于互斥。男人，女人。 set ：能取多个。

(1)枚举

mysql> create table kdata12(id set('m','y'));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into kdata12 values('m'),('y');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from kdata12;
+------+
| id   |
+------+
| m    |
| y    |
+------+
2 rows in set (0.03 sec)

(2)Set

mysql> create table kdata13(type set('a','b','c','d','f'));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into kdata13 values('a');
Query OK, 1 row affected (0.05 sec)

mysql> insert into kdata13 values('a,b');
Query OK, 1 row affected (0.03 sec)

mysql> insert into kdata13 values('a,a,a,b'); ###不能写重复选项，不然无效
Query OK, 1 row affected (0.01 sec)

mysql> select  * from  kdata13;
+------+
| type |
+------+
| a    |
| a,b  |
| a,b  |
+------+
3 rows in set (0.00 sec)

mysql> insert into kdata13 values('e');  ###如果写不存在选项，直接报错
ERROR 1265 (01000): Data truncated for column 'type' at row 1