拓展 :
查看表的大小,从而能知道数据类型的最优化---- information_schema数据库，这个数据库中装的是mysql的元数据，包括数据库信息、数据库中表的信息等。所以要想查询数据库占用磁盘的空间大小可以通过对information_schema数据库进行操作
1> use information_schema;
-- 如果想看指定数据库中的数据表，可以用如下语句：
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='anzhipay' and table_name='cs_apply_record';
2> 如果想看数据库中每个数据表的，可以用如下语句：
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='anzhipay';
mysql支持多种数据类型，数据类型主要有下面三种：
数值型+ 日期/时间类型 + 字符串类型
1) 整数类型
不同数据类型有不同的取值范围，可存储的值的范围越大，则所需的存储空间也越大,这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围
ZEROFILL 修饰符规定 0（不是空格）可以用来充输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。
整数类型主要有：
tinyint(byte), smallint (short), mediumint, int(integer) --int, bigint( long)
整数类型的属性且为 primary key 可以添加auto_increment自增约束条件。
| 类型名称 | 说明 | 存储需求 | 有符号数 | 无符号数 |
| tinyint | 很小的整数 | 1个字节 | -129~127 | 0~255 |
| smallint | 小的整数 | 2个字节 | -32768~32767 | 0~65535 |
| mediumint | 中等大小的整数 | 3个字节 | -8388608~8388607 | 0~16777215 |
| int(integer) | 普通大小的整数 | 4个字节 | -2147483648~2147483647 | 0~4294967295 |
| bigint | 大整数 | 8个字节 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
它们的范围是-2的(n-1)次方到2的(n-1)次方-1(n是位数)。如果选择了UNSIGNED表示非负，它可以使整数最大值提高一倍，有符号和无符号使用相同的存储空间，具有相同的性能
示例：
drop TABLE if EXISTS tb_emp1;
create table tb_emp1
( id int(11) ZEROFILL, -- ZEROFILL 不足指定位数的 0补充,只能在mysql客户端看到,navicat不行
name varchar(25),
deptId int(11),
salary float
);id字段的数据类型为int(11)，数字11是该数据类型指定的显示宽度，是指定能够显示的数值中数字的个数,不会限制值的合法范围(由自身类型决定 ),对于INT(1)和INT(20)来说存储的空间和计算是一样的，因此指定位数是没有意义的???。比如你向INT(1)中插入了123456值，数据库中其实已经存入了123456，只是对于客户端查出来是1而已
数值的位数小于指定的宽度则会由空格填充(如果指定ZEROFILL 将会由0补充),如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值仍可以插入,超过范围时报错 :Out of range value for 字段名
如果不指定显示宽度，则系统会为每一种类型指定默认的宽度值。
示例：
drop TABLE if EXISTS tmp1;
create table tmp1
( x tinyint,
y samllint,
z mediumint,
m int,
n bigint
);
desc tmp1; 系统添加的默认显示宽度可以保证取到取值范围内的所有值。
2) FLOAT、DOUBLE 和 DECIMAL 类型(浮点数类型和定点数类型 )
比如，我们发的工资，一般都带有小数
| 类型名称 | 说明 | 存储需求 |
| float | 单精度浮点数 | 4个字节 |
| double | 双精度浮点数 | 8个字节 |
| decimal(M,D), dec | 压缩的严格定点数 | M+2个字节 |
注：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。
DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位
decimal不同于float和double，decimal是以串存放的，decimal可能的最大取值范围和double一样，但其有效的取值范围由M和D决定。decimal的存储空间并不是固定的，而是由M决定的。
不论是定点还是浮点类型，用户指定的精度超出精度范围，则会四舍五入进行处理。
示例：
drop TABLE if EXISTS tmp2;
create table tmp2
(x float(5,1),
y double(5,1),
z decimal(5,1)
);-- 向其中插入数据
insert into tmp2 values (5.12, 5.15, 5.123);
会出现警告，float和double在四舍五入不会警告，但是decimal会。
float和double如果不指定精度，会按照实际的精度（由计算机硬件和操作系统共同决定）
而decimal如不指定则默认为(10,0)
mysql中，定点数是以字符串的形式存储，如果对精度的要求比较高，使用decimal比较好，另外两个浮点数在进行加减法和比较运算时也容易出问题。
使用浮点数要尽量避免做比较运算。
3) 字符串类型
字符串类型除了可以储存字符串类型之外，还可以存储图片和声音的二进制数据,每个字符串列可以定义自己的字符集和排序规则，或者说校对规则，这些东西很大程度上影响性能。
字符串可以进行区分或者不区分大小写的比较，还可以模糊查找。
char(M) M代表的是字符个数具体参考: https://blog.csdn.net/qq_31459039/article/details/94559538
| 类型名称 | 说明 | 存储需求 |
| char(M) | 固定长度非二进制字符串 | M字节，1<=M<=255 |
| varchar(M) | 变长非二进制字符串 | L+1字节，在此L<=M和1<=M<=255 |
| tinytext | 非常小的非二进制字符串 | L+1字节，L<2^8 |
| text | 小的二进制字符串 | L+2字节，L<2^16 |
| mediumtext | 中等大小的二进制字符串 | L+3字节，L<2^24 |
| longtext | 大的二进制字符串 | L+4字节，L<2^32 |
| enum | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目 |
| set | 字符串对象可以有0个或者多个set成员 | 1，2，3，4或8，取决于集合成员的数量 |
varchar, blob和text类型为变长类型，其存储需求取决于列值的实际长度而不是取决于最大的可能取值。
VARCHAR(10)是变长的（不仅需要10个字节，还需要记录长度，如果长度小于255则额外需要1个字节，如果大于255则需要两个字节）
char和varchar
char(M)为固定长度字符串，在定义时指定字符串列长，右侧的空格会被删除
varchar的实际空间要看存的字符有多长，而且保留右侧多的空格
select concat('(',ch,')'),concat('(',vch,')') from tmp8;
ch在保存'ab '时删除了最后的两个空格，而vch则会保留。
字符串类型(M) 这个M是长度的意思，插入该列的字符串长度将不允许超过M所指定的长度
char最长可取255；
varchar最长可取65535；
注意: 1.int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符
2.char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
3.varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符
CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MySQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。
因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。
char是固定长度的，查询速度比varchar速度快的多。char的缺点是浪费存储空间。
检索char列时，返回的结果会删除尾部空格，所以程序需要对为空格进行处理。
对于长度变化不大且对查询速度有较高要求的数据可以考虑使用char。
随着MySQL的不断升级，varchar的性能不断改进并提高。
存储引擎使用原则：
MyISAM：建议使用固定长度列代替可变长度列。
InnoDB：建议使用varchar类型
4) TEXT 和 BLOB 类型
对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。
5) 日期和时间类型
在处理日期和时间类型的值时，MySQL 带有5个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
| 类型 | 大小 | 范围 | 格式 | 用途 |
| DATE日期型 | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME时间型 | 3 | ''-838:59:59''/''838:59:59'' | HH:MM:SS | 时间值或持续时间 |
| YEAR年型 | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME日期时间型 | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP时间戳型 | 8 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳
|
year
year是一个用来表示年份的单字节类型。
以4位数字或者字符来表示，范围为'1901~2155'，输入格式为'YYYY'或者YYYY
如果只输入两位字符则会分别被转换，'00'~'69'转换为2000~2069，'70'~'99'被转换为1970~1999，'0'和'00'的转换相同
超过了取值范围，均转换为2000，呵呵，超过了范围明明就error了。
输入两位数字的或区别在于0
数字格式的0将变为0000，而字符则会转换为2000。
delete from tmp3; //删除了所有的行
time
格式为'HH:MM:SS'，需要3个字节，范围为-838：59：59~838：59：59，小时部分如此大是因为time不仅可以表示一天的时间，还可以是时间间隔。
如果没有冒号，则mysql觉得最右边两位是秒，不是当天的时间，而是过去了的时间。
如果有冒号则肯定是一天中的时间。
向表中插入系统时间
insert into tmp4 values (current_time),(now());
date
仅需要日期，不需要小时和分秒，需要3个字节。
insert into tmp4 values (current_date),(now());
current_time只返回当前时间，current_date只返回当前日期
now()返回日期和时间值。
datetime
需要同时包含日期和时间信息，要8个字节
timestamp
timestamp显示的格式和datetime相同，显示宽度固定在19个字符，需要4个字节，取值范围比datetime要小。
timestamp的值是以世界标准时间存储的，存取时对当前时区进行转换。
set time_zone='+10:00'
向datetime或者timestamp分配date对象的值，则时间部分均为'00:00:00'
datetime和timestamp两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它,可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间
另外TimeStamp类型与DateTime在无输入的情况下要注意下：
- TimeStamp使用Current_TimeStamp()而DateTime使用NOW(来获取当前时间)；
- 输入NULL时，系统会输入系统当前日期与时间；
- 无任何输入时，系统会输入系统当前日期与时间；
timestamp格式同datetime，但在存储时需要4个字节(datetime需要8字节)，并且以UTC（世界标准时间）进行存储（即timestamp会随设置的时区而变化，而datetime存储的绝不会变化）；timestamp的范围：1970-2037,用整数保存时间戳不方便，因为取出来之后还需要程序转换下，所以不推荐这样做。
timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。
1.根据实际需要选择能够满足应用的最小存储日期类型。
2.如果记录年月日时分秒，并且记录年份比较久远，最好使用datetime，不要使用timestamp。
3.如果记录的日期需要让不同时区的用户使用，最好使用timestamp，因为日期类型中只有它能够和实际时区相对应。
timestamp有两个属性，分别是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP两种，使用情况分别如下：
1. CURRENT_TIMESTAMP
当要向数据库执行insert操作时，如果有个timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间
2. ON UPDATE CURRENT_TIMESTAMP
当执行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP属性。则字段无论值有没有变化，它的值也会跟着更新为当前UPDATE操作时的时间。
-- 自动初始化和更新:
modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP=modify_time TIMESTAMP
-- 只自动初始化:
modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- 只自动更新
modify_time TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
-- 只是给一个常量（注：0000-00-00 00:00:00）
modify_time TIMESTAMP DEFAULT 0
一个表可以存在多个TIMESTAMP列，但只有一个列会根据数据更新而改变为数据库系统当前值。因此，一个表中有多个TIMESTAMP 列是没有意义，实际上一个表只设定一个TIMESTAMP列,正确的设置:`recharge_time` TIMESTAMP NULL DEFAULT NULL
`transfer_time` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
一般建表时候，创建时间用datetime，更新时间用timestamp。
drop table if EXISTS class_member;
CREATE TABLE class_member(
id TINYINT(2) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE,
age TINYINT(2) NOT NULL,
create_time DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
modify_time TIMESTAMP
); //插入更新 查看值6)复合类型enum
enum是用来存储枚举值的
字段名 enum('值1', '值2', ... , '值n')
取值时只能在指定的枚举列表中取，而且一次只能取1个，如果创建的成员中有空格时，将自动的删除空格。
enum的值在内部用整数表示，每个枚举值均有一个索引值，从1开始编号，最多枚举65535个元素
enum('first', 'second', 'third')
| 值 | 索引 |
| null | null |
| '' | 0 |
| first | 1 |
| second | 2 |
| third | 3 |
示例：
create table tmp9 (enm enum('first', 'second', 'third'));
insert into tmp9 values ('first'), ('second'), ('third'), (null);
select enm, enm+0 from tmp9;
目测不能插入''
enum列总有一个默认的值，如果将enum列声明为null，null则为该列的一个有效值，并且默认为null
如果enum列被声明为not null，则默认值为列表的第一个元素
enum中的值都是以编号存在的
插入编号值和插入枚举值的效果是一样的。
复合类型set
set是一个字符串对象，可以有一个或者是多个值，最多有64个成员。
set('值1', '值2', ... , '值n')
如果set字段中列值又重复，则mysql会自动的删除重复的值
插入的set字段值顺序并不重要，存入时会按照定义的顺序显示
如果插入了不正确的值，将会报错。
二进制类型 太多了就不一一写了
字段修饰符
在声明字段的时候，可以为这些字段添加额外的修饰符，不同的数据类型，其修饰符也是不一样的，
| 数据类型 | 常见的修饰符 |
|
整型 | UNSIGNED：无符号，表示非负。（UNSIGNED要定义在NULL或NOT NULL之前） NULL：允许为空 NOT NULL：允许非空 DEFAULT N：定义默认值 AUTO_INCREMNET：自动增长，自动增长的字段必须是整型、非空、非负、唯一键或主键。 |
| 浮点型float | UNSIGNED,NULL，NOT NULL,DEFAULT N |
|
字符型（char，varchar和text） | NULL,NOT NULL,DEFAULT 'string' CHARACTER SET 字符集：设置字符集。 show character set可以查看mysql支持的所有字符集； show variables like ‘%charac%’可以查看mysql支持的默认字符集。 collation '排序规则'：设定排序规则； show collation可以查看mysql支持的所有排序规则； |
| 字符型（binary、varbinary和blob） | NULL,NOT NULL,DEFAULT（但不适用于BLOB类型） |
| 日期时间型 时间戳型 TIMESTAMP | NULL,NOT NULL,DEFAULT ‘string' NOT NULL DEFAULT 0 /‘string' recharge_time TIMESTAMP null DEFAULT NULL 前面的加个null |
| 枚举型（ENUM） | NULL,NOT NULL,DEFAULT 'string' |
例如：CREATE TABLE test(ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20))
email varchar(50) unique 注：唯一但是可以为空(空和空不相等)
email varchar(50) == email varchar(50) null -- 默认值就是null
总结:
数据类型的选择
整型：根据要显示的最大值决定；
浮点型：要显示小数。如果要精确到小数点后10位，就选择DOUBLE，而不应该选择FLOAT。DECIMAL精度较高，浮点数会出现误差，如果精度较高，则应选择定点数DECIMAL；
字符串型：定长与变长的区别，CHAR类型占用空间比较大，但是处理速度比VARCHAR快，如果长度变化不大，如身份证号码那种，最好选择CHAR类型。而对于评论字符串，最好选择VARCHAR；
时间：根据需要显示的类型咯，特别是TIMESTAMP,如果需要显示的时间与时区对应，就应该选择TIMESTAMP；
ENUM类型和SET类型：长度不同，ENUM类型最多可以由65535个成员，而SET类型最多只能包含64个成员。且ENUM只能单选，而SET类型可以多选；
TEXT类型和BLOB类型：TEXT只能存储字符数据，而BLOB可以存储二进制数据。如果是纯文本，适合TEXT。如果是图片等适合存二进制；
问题总结
1、存储路径的问题
MySQL中，如果路径中使用"\"符号时，这个符号会被过滤。解决的办法是路径中用"/"或"\\"来代替"\"。这样MySQL就不会自动过滤路径中的分隔符。
2、MySQL中的布尔类型
MySQL中没有Bool或Boolean类型，但是为了支持SQL标准，也可以定义Bool或Boolean类型的，但是Bool或Boolean类型最后转换成的是TinyInt(1)，也就是说,在MySQL中，布尔类型实际上是TinyInt(1)。
3、MySQL中如何存储JPG图片或MP3音乐
一般情况下，数据库中不直接存储图片和音频文件，而是存储图片或音频文件的路径，如果在特殊情况下需要在MySQL数据库中存储图片和音频文件，可以选择BLOB类型；
简单的原则：
A、通常最小的是最好的
因为这样可以用更少的磁盘、内容、CPU缓存，大大减少IO开销。
B、简单就好
简单的数据类型操作通常需要更少的CPU周期。例如，整型比字符操作代价更小，因为字符集和校对规则（排序规则）使它比整型更复杂。比如应该使用MySQL内建的类型而不是使用字符型来存储日期和时间。
C、尽量避免使用NULL
NULL是列默认的属性，通常我们要指定为NOT NULL。有NULL的列值会使得索引、索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要对它进行特殊处理，当可为NULL列做索引时，每个索引需要一个额外的字节，在MyISAM更有可能导致固定大小的索引变成可变大小索引，在InnoDB中使用单独的位(bit)存储NULL值。
转载自: https://www.cnblogs.com/tuhooo/p/5442722.html
https://blog.csdn.net/libo222/article/details/51508379
https://blog.csdn.net/ja_java/article/details/69255904
https://blog.csdn.net/timchen525/article/details/75194319
拓展:
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
SQL一共分成四类，如下所示：
l 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等，例如创建、删除、修改：数据库、表结构等；
l 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新，例如：增、删、改表记录；
l 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户；
l 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。
大家可能会对DDL和DML分的不太清楚，DDL是对数据库或表的结构进行操作，而DML是对表的记录进行操作(增、删、改)。以上四个分类，我们以后最常用的就是DDL、DML、DQL，其中，DQL是难点。
SELECT VERSION(); -- 查询数据库版本
select database(); -- 查询当前数据库名称
show databases; -- 展示出所有的数据库
show TABLES; -- 展示出当前数据库下 所有的表
扫一扫
1272
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
