13518219792
一：MySQL数据类型
成都创新互联公司主要从事网站设计制作、网站制作、网页设计、企业做网站、公司建网站等业务。立足成都服务肃州,10余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575
MySQL中定义数据字段的类型对你数据库的优化是非常重要的
MySQL支持多种数据类型，大致可以分为三类：数值 日期/时间和字符串
二、数值类型
1.整数类型
2.浮点数
如果希望保证值比较准确，推荐使用定点数数据类型。MySql中的浮点类型有float，double和real。他们定义方式为：FLOAT(M,D) 、 REAL(M,D) 、 DOUBLE PRECISION(M,D)。
FLOAT和DOUBLE中的M和D的取值默认都为0，即除了最大最小值，不限制位数。允许的值理论上是-1.7976931348623157E+308~-2.2250738585072014E-308、0和2.2250738585072014E-308~1.7976931348623157E+308。M、D范围如下：
（MySql5.7实测，与IEEE标准计算的实际是不同的，下面介绍）：M取值范围为0~255。FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M=6时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。
D取值范围为0~30，同时必须=M。double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M=16时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。
CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉，所以，我们在存储时字符串右边不能有空格，即使有，查询出来后也会被删除。在存储或检索过程中不进行大小写转换。
三、时间日期类型（5）
该“0”值如下：
请点击输入图片描述
四、各种类型占用的存储
1.数值类型
请点击输入图片描述
定点数的比较特殊，而且与具体版本也有关系，此处单独解释：
使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：
请点击输入图片描述
2.时间日期
请点击输入图片描述
从版本5.6.4开始，存储需求就有所改变，根据精度而定。不确定部分需要的存储如下：
请点击输入图片描述
比如，TIME(0), TIME(2), TIME(4), 和TIME(6) 分别使用3, 4, 5, 6 bytes。
3.字符串
请点击输入图片描述
4.类型的选择
为了优化存储，在任何情况下均应使用最精确的类型。
例如，如果列的值的范围为从1到99999，若使用整数，则MEDIUMINT UNSIGNED是好的类型。在所有可以表示该列值的类型中，该类型使用的存储最少。
用精度为65位十进制数(基于10)对DECIMAL 列进行所有基本计算(+、-、*、/)。
使用双精度操作对DECIMAL值进行计算。如果准确度不是太重要或如果速度为最高优先级，DOUBLE类型即足够了。为了达到高精度，可以转换到保存在BIGINT中的定点类型。这样可以用64位整数进行所有计算，根据需要将结果转换回浮点值。
5.使用其他数据库的SQL语句
为了使用为其它数据库编写的SQL执行代码，MySQL按照下表所示对列类型进行映射。通过这些映射，可以很容易地从其它数据库引擎将表定义导入到MySQL中：
请点击输入图片描述
背景
MySQL 一直以来都有 TEXT、BLOB 等类型用来存储图片、视频等大对象信息。比如一张图片，随便一张都 5M 以上。视频也是，随便一部视频就是 2G 以上。
假设用 MySQL 来存放电影视频等信息，一部是 2G，那么存储 1000 部就是 2TB，2TB 也就是 1000 条记录而已，但是对数据库性能来说，不仅仅是看记录数量，更主要的还得看占用磁盘空间大小。空间大了，所有以前的经验啥的都失效了。
所以一般来说存放这类信息，也就是存储他们的存放路径，至于文件本身存放在哪里，那这就不是数据库考虑的范畴了。数据库只关心怎么来的快，怎么来的小。
举例
虽然不推荐 MySQL 这样做，但是也得知道 MySQL 该怎么做才行，做到心里有数。比如下面一张微信图片，大概 5M 的样子。
root@ytt:/var/lib/mysql-files# ls -sihl 微信图片_20190711095019.jpg274501 5.4M -rw-r--r-- 1 root root 5.4M Jul 11 07:17 微信图片_20190711095019.jpg
拷贝 100 份这样的图片来测试
root@ytt:/var/lib/mysql-files# for i in `seq 1 100`; do cp 微信图片_20190711095019.jpg "$i".jpg;done;
root@ytt:/var/lib/mysql-files# ls
100.jpg 17.jpg 25.jpg 33.jpg 41.jpg 4.jpg 58.jpg 66.jpg 74.jpg 82.jpg 90.jpg 99.jpg f8.tsv
10.jpg 18.jpg 26.jpg 34.jpg 42.jpg 50.jpg 59.jpg 67.jpg 75.jpg 83.jpg 91.jpg 9.jpg 微信图片_20190711095019.jpg
1111.jpg 19.jpg 27.jpg 35.jpg 43.jpg 51.jpg 5.jpg 68.jpg 76.jpg 84.jpg 92.jpg f1.tsv
11.jpg 1.jpg 28.jpg 36.jpg 44.jpg 52.jpg 60.jpg 69.jpg 77.jpg 85.jpg 93.jpg f2.tsv
12.jpg 20.jpg 29.jpg 37.jpg 45.jpg 53.jpg 61.jpg 6.jpg 78.jpg 86.jpg 94.jpg f3.tsv
13.jpg 21.jpg 2.jpg 38.jpg 46.jpg 54.jpg 62.jpg 70.jpg 79.jpg 87.jpg 95.jpg f4.tsv
14.jpg 22.jpg 30.jpg 39.jpg 47.jpg 55.jpg 63.jpg 71.jpg 7.jpg 88.jpg 96.jpg f5.tsv
15.jpg 23.jpg 31.jpg 3.jpg 48.jpg 56.jpg 64.jpg 72.jpg 80.jpg 89.jpg 97.jpg f6.tsv
16.jpg 24.jpg 32.jpg 40.jpg 49.jpg 57.jpg 65.jpg 73.jpg 81.jpg 8.jpg 98.jpg f7.tsv
我们建三张表，分别用 LONGBLOB、LONGTEXT 和 VARCHAR 来存储这些图片信息
mysql show create table tt_image1G
*************************** 1. row ***************************
Table: tt_image1
Create Table: CREATE TABLE `tt_image1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`image_file` longblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql show create table tt_image2G
*************************** 1. row ***************************
Table: tt_image2
Create Table: CREATE TABLE `tt_image2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`image_file` longtext,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql show create table tt_image3G
*************************** 1. row ***************************
Table: tt_image3
Create Table: CREATE TABLE `tt_image3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`image_file` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
我们来给三张表插入 100 张图片（插入前，建议把 max_allowed_packet 设置到最大）
tt_image1
root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;
do mysql -S /var/run/mysqld/mysqld.sock -e "insert into ytt.tt_image1(image_file)
values (load_file('/var/lib/mysql-files/$i.jpg'))";done;
tt_image2
root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;
do mysql -S /var/run/mysqld/mysqld.sock -e "insert into ytt.tt_image2(image_file)
values (hex(load_file('/var/lib/mysql-files/$i.jpg')))";done;
tt_image3
root@ytt:/var/lib/mysql-files# aa='begin;';for i in `seq 1 100`;
do aa=$aa"insert into ytt.tt_image3(image_file) values
('/var/lib/mysql-files/$i.jpg');";
done;aa=$aa'commit;';mysql -S /var/run/mysqld/mysqld.sock -e "`echo $aa`";
检查下三张表记录数
mysql select 'tt_image1' as name ,count(*) from tt_image1 union allselect 'tt_image2',count(*) from tt_image2 union all select 'tt_image3', count(*) from tt_image3;+-----------+----------+| name | count(*) |+-----------+----------+| tt_image1 | 100 || tt_image2 | 100 || tt_image3 | 100 |+-----------+----------+3 rows in set (0.00 sec)
看下文件大小，可以看到实际大小排名，LONGTEXT 字段存储的最大，LONGBLOB 字段缩小到一半，最小的是存储图片路径的表 tt_image3。所以这里从存储空间来看，存放路径最占优势。
root@ytt:/var/lib/mysql/ytt# ls -silhS tt_image*274603 1.1G -rw-r----- 1 mysql mysql 1.1G Jul 11 07:27 tt_image2.ibd274602 545M -rw-r----- 1 mysql mysql 544M Jul 11 07:26 tt_image1.ibd274605 80K -rw-r----- 1 mysql mysql 112K Jul 11 07:27 tt_image3.ibd
那么怎么把图片取出来呢？
tt_image3 肯定是最容易的
mysql select * from tt_image3;+----+----------------------------+| id | image_file |+----+----------------------------+| 1 | /var/lib/mysql-files/1.jpg |+----+----------------------------+...100 rows in set (0.00 sec)
tt_image1 直接导出来二进制文件即可，下面我写了个存储过程，导出所有图片。
mysql DELIMITER $$mysql USE `ytt`$$mysql DROP PROCEDURE IF EXISTS `sp_get_image`$$mysql CREATE DEFINER=`ytt`@`localhost` PROCEDURE `sp_get_image`()mysql BEGIN DECLARE i,cnt INT DEFAULT 0; SELECT COUNT(*) FROM tt_image1 WHERE 1 INTO cnt; WHILE i cnt DO SET @stmt = CONCAT('select image_file from tt_image1 limit ',i,',1 into dumpfile ''/var/lib/mysql-files/image',i,'.jpg'''); PREPARE s1 FROM @stmt; EXECUTE s1; DROP PREPARE s1; SET i = i + 1; END WHILE; END$$mysql DELIMITER ;mysql call sp_get_image;
tt_image2 类似，把 select 语句里 image_file 变为 unhex(image_file) 即可。
总结
这里我举了个用 MySQL 来存放图片的例子，总的来说有以下三点：
占用磁盘空间大（这样会带来各种各样的功能与性能问题，比如备份，写入，读取操作等）
使用不易
还是推荐用文件路径来代替实际的文件内容存放
实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用
DEClMAL 存储比 BIGINT还大的整数。 MySQL 既支持精确类型，也支持不精确类型。
FLOAT 和 DOUBLE 类型支持使用标准的浑点运算进行近似计算。如果需要知道浮点运算是
怎么计算的，则需要研究所使用的平台的浮点数的具体实现。
DECIMAL 类型用于存储精确的小数。在 MySQL 5.0 和更高版本， DECIMAL 类型支持精确 计算。 MySQL 4.1 以及更早版本则使用浮点运算来实现 DECIAML 的计算，这样做会因为 精度损失导致一些奇怪的结果。在这些版本的 MySQL 中，DEClMAL 只是一个"存储类型"。
因为 CPU 不支持对 DEClMAL 的直接计算，所以在 MySQL 5.0 以及更高版本中， MySQL 服务器自身实现了 DECIMAL 的高精度计算。相对而言， CPU 直接支持原生浮点计算，所 以浮点运算明显更快。
浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点前后所允许的 最大位数。这会影响列的空间消耗。 MySQL 5.0 和更高版本将数字打包保存到一个二进 制字符串中(每 4个字节存 9个数字)。例如， DEClMAL(18 ，9)小数点两边将各存储 9个 数字，一共使用 9 个字节:小数点前的数字用 4 个字节，小数点后的数字用 4 个字节， 小数点本身占 1个字节。
MySQL 5.0 和更高版本中的 DEClMAL 类型允许最多 65个数字。而早期的 MySQL 版本中 这个限制是254个数字，并且保存为未压缩的字符串(每个数字一个字节)。然而，这些(早 期)版本实际上并不能在计算中使用这么大的数字，因为 DECIMAL 只是一种存储格式: 在计算中 DEClMAL 会转换为 DOUBLE 类型。
有多种方法可以指定浮点列所需要的精度，这会使得 MySQL 悄悄选择不同的数据类型， 或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型， 不指定精度。
浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。 FLOAT 使用 4个字 节存储。 DOUBLE 占用8个字节，相比 FLOAT有更高的精度和更大的范围。和整数类型一样， 能选择的只是存储类型 IMySQL 使用 DOUBLE 作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用
DECIMAL一一例如存储财务数据。但在数据量比较大的时候，可以考虑使用 BIGINT 代替
DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财
务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在 BIGI町里，
这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。
