mysql数据类型和常用字段属性总结

 

MySQL中的数据类型大的方面来分，可以分为：日期和时间、数值，字符串，以及二进制。

一，日期和时间数据类型

MySQL数据类型	含义
date	3字节，日期，格式：2014-09-18
time	3字节，时间，格式：08:42:30
datetime	8字节，日期时间，格式：2014-09-18 08:42:30
timestamp	4字节，自动存储记录修改的时间
year	1字节，年份

DATE、TIME 和 YEAR 类型

MySQL 用 DATE 和 YEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。

需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 YEAR 类型输入的两个数字进行最大限度的通译。因为所有 YEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。

DATETIME 和 TIMESTAMP 类型

除了日期和时间数据类型，MySQL 还支持 DATETIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

 

二，数值数据类型

注意浮点型

MySQL数据类型	含义
float(m, d)	4字节，单精度浮点型，m总个数，d小数位
double(m, d)	8字节，双精度浮点型，m总个数，d小数位
decimal(m, d)	decimal是存储为字符串的浮点数
real	double的同义词

我在MySQL中建立了一个表，有一列为float(5, 3)；做了以下试验：

1.插入123.45678，最后查询得到的结果为99.999；
2.插入123.456，最后查询结果为99.999；
3.插入12.34567，最后查询结果为12.346；

所以，在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准。

在 WHERE 子句搜索条件中（特别是 = 和 <> 运算符），应避免使用 float 或 real 列。最好限制使用 float 和 real 列做 > 或 < 的比较。

 

三，字符串数据类型

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

1.char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。
2.同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
3.超过char和varchar的n设置后，字符串会被截断。
4.char的上限为255字节，varchar的上限65535字节，text的上限为65535。
5.char在存储的时候会截断尾部的空格，varchar和text不会。
6.varchar会使用1-3个字节来存储长度，text不会。

 

四，二进制类型（很少用到，不会使用二进制数据）

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^3

 

1.bit（位）

在表里面具体展示为0011001这种形式

2.blob（二进制大对象）

类型就统一展示位(BLOB),没有具体的形式

3.binary（固定二进制字符串）

概念解析：二进制字符串，也叫字节字符串和字符字符串不一样，没有字符集的概念；

php里面有两个函数：pack()和unpack()函数，可以使字符串和二进制之间相互转换；

本质是 8个位的二进制；

4.binary（可变长二进制字符串）

概念解析：二进制字符串，也叫字节字符串和字符字符串不一样，没有字符集的概念；

php里面有两个函数：pack()和unpack()函数，可以使字符串和二进制之间相互转换；

本质是 8个位的二进制；

 

五，其它类型

1.enum（单枚举）

enum数据类型就是定义了一种枚举，最多包含65535个不同的成员。

当定义了一个enum的列时，该列的值限制为列定义中声明的值中的一个。

如果列声明包含 NULL属性，则NULL将被认为是一个有效值，并且是默认值。

如果声明了NOT NULL，则列表的第一个成员是默认值。

enum(“member1″, “member2″, … “member65535″)

2.set（集合：多枚举）

set数据类型为指定一组预定义值中的零个或多个值提供了一种方法，这组值最多包括64个成员。值的选择限制为列定义中声明的值。

set(“member”, “member2″, … “member64″)

 

数据类型属性

上面大概总结了MySQL中的数据类型，当然了，上面的总结肯定是不全面的，如果要非常全面的总结这些内容，好几篇文章都不够的。下面就再来总结一些常用的属性。

1.auto_increment（自增）

auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。

MySQL要求将auto_increment属性用于作为主键的列。此外，每个表只允许有一个auto_increment列。例如：

id smallint not null auto_increment primary key

2.binary（排序是否区分大小写）

binary属性只用于char和varchar值。当为列指定了该属性时，将以区分大小写的方式排序。与之相反，忽略binary属性时，将使用不区分大小写的方式排序。例如：

hostname char(25) binary not null

3.default（默认值）

default属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为MySQL不允许插入函数或表达式值。此外，此属性无法 用于BLOB或TEXT列。如果已经为此列指定了NULL属性，没有指定默认值时默认值将为NULL，否则默认值将依赖于字段的数据类型。例如：

subscribed enum('0', '1') not null default '0'

4.index（索引）

如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。

索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。

以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升。

create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);

我们也可以利用MySQL的create index命令在创建表之后增加索引：

create index lastname on employees (lastname(7));

这一次只索引了名字的前7个字符，因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好，所以应当在实践中尽量使用小的索引。

5.null 和 not null（空与非空）

如果将一个列定义为not null，将不允许向该列插入null值。建议在重要情况下始终使用not null属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。

为列指定null属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null精确的说法是“无”，而不是空字符串或0。

6.primary key（主键）

primary key属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予auto_increment属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。主键又分为以下两种：

(1)单字段主键

如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使用单字段主键。注意，此主键一旦设置就不能再修改。

(2)多字段主键

如果记录中任何一个字段都不可能保证唯一性，就可以使用多字段主键。这时，多个字段联合起来确保唯一性。

7.unique（唯一性）

被赋予unique属性的列将确保所有值都有不同的值，只是null值可以重复。一般会指定一个列为unique，以确保该列的所有值都不同。例如：

email varchar(45) unique

8.zerofill（零填充）

zerofill属性可用于任何数值类型，用0填充所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的int值为4时，将表示它为0000000004。例如：

orderid int unsigned zerofill not null

9.unsigned（非负）

unsigned属性用于数值类型，表示非负数值；会影响数值的取值区间

 

合理的选择数据类型

选择合理范围内最小的

我们应该选择最小的数据范围，因为这样可以大大减少磁盘空间及磁盘I/0读写开销，减少内存占用，减少CPU的占用率。

选择相对简单的数据类型

数字类型相对字符串类型要简单的多，尤其是在比较运算时，所以我们应该选择最简单的数据类型，比如说在保存时间时，因为PHP可以良好的处理LINUX时间戳所以我们可以将日期存为int(10)要方便、合适、快速的多。

不要使用null

为什么这么说呢，因为MYSQL对NULL字段索引优化不佳，增加更多的计算难度，同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NULL。有些值他确实有可能没有值，怎么办呢？解决方法是数值弄用整数0，字符串用空来定义默认值即可。

字符串类型的使用

字符串数据类型是一个万能数据类型，可以储存数值、字符串、日期等。
保存数值类型最好不要用字符串数据类型，这样存储的空间显然是会更大，而且在排序时字符串的9是大于22的，其实如果进行运算时mysql会将字符串转换为数值类型，大大降低效果，而且这种转换是不会走原有的索引的。
如果明确数据在一个完整的集合中如男，女，那么可以使用set或enum数据类型，这种数据类型在运算及储存时以数值方式操作，所以效率要比字符串更好，同时空间占用更少。

　　VARCHAR

是可变长度字符串类型，那么即然长度是可变的就会使用1，2个字节来保存字符的长度，如果长度在255内使用1个字节来保存字符长度，否则使用2个字符来保存长度。由于varchar是根据储存的值来保存数据，所以可以大大节约磁盘空间。
如果数据经常被执行更新操作，由于VARCHAR是根据内容来进行储存的，所以mysql将做更多的工作来完成更新操作，如果新数据长度大于老数据长度一些存储引擎会进行拆分操作处理。同时varchar会完全保留内部所有数据，最典型的说明就是尾部的空格。

　　CHAR

固定长度的字符串保存类型，CHAR会去掉尾部的空格。在数据长度相近时使用char类型比较合适，比如md5加密的密码用户名等。
如果数据经常进行更新修改操作，那么CHAR更好些，因为char长度固定，性能上要快。

数值类型的选择

数值数据类型要比字符串执行更快，区间小的数据类型占用空间更少，处理速度更快，如tinyint可比bigint要快的多

选择数据类型时要考虑内容长度，比如是保存毫米单位还是米而选择不同的数值类型

整数

整数类型很多比如tinyint、int、smallint、bigint等，那么我们要根据自己需要存储的数据长度决定使用的类型，同时tinyint(10)与tinyint(100)在储存与计算上并无任何差别，区别只是显示层面上，但是我们也要选择适合合适的数据类型长度。可以通过指定zerofill属性查看显示时区别。

浮点数与精度数值

浮点数float在储存空间及运行效率上要优于精度数值类型decimal，但float与double会有舍入错误而decimal则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。

一些常用字段举例

姓名：char(20)
价格：DECIMAL(7, 3)
产品序列号：SMALLINT(5) unsigned
文章内容: TEXT
MD5: CHAR(32)
ip: char(15)
time: int(10)
email char(32)

 

地理空间数据类型

参考：

https://www.pianshen.com/article/8799417663/

MySQL支持以下数据类型：

   Geometry:可以存储所有的几何类型

   Point:简单点

   LINESTRING:简单线

   POLYGON:简单面

   MULTIPOINT：多点

   MULITILINESTRING:多线

   MUILITIPOLYGON：很多方面

   GEOMETRYCOLLECTION:任何几何集合