Mysql中有哪些建表规范

今天就跟大家聊聊有关Mysql中有哪些建表规范，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

一、库名、表名、字段名必须使用小写字母，“_”分割；库名、表名、字段名必须不超过12个字符；库名、表名、字段名见名知意,建议使用名词而不是动词。

二、建议使用InnoDB存储引擎。

存储引擎：innoDb支持事物，myisam不支持事物，建议使用innoDb， 5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好。

（1） MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：

1.tb_demo.frm，存储表定义；

2.tb_demo.MYD，存储数据；

3.tb_demo.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

（2） InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。 4.外键约束。MySQL支持外键的存储引擎只有InnoDB。 5.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

（3）  MEMORY   ：使用MySQL Memory存储引擎的出发点是速度。

1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

（2） MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景

三、存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

（1） decimal 类型可以精确地表示非常大或非常精确的小数。大至 1028（正或负）以及有效位数多达 28 位的数字可以作为 decimal类型存储而不失其精确性。该类型对于必须避免舍入错误的应用程序（如记账）很有用。

1、   decimal   类型是适合财务和货币计算的   128   位数据类型。

2、   decimal不是浮点型、decimal不存在精度损失；

3、   decimal所能储存的数比double大，从double到decimal的类型转换不会出现任何问题。

4、

float 单精度浮点 32bit，

double 双精度浮点64bit，

decimal是高精度 128bit，浮点型。

float double 是 基本类型（primitive type），decimal不是。

5、  浮点数运算会有精度损失问题，有精度损失时程序不会报告，要程序员自己注意。

（2)   mysql中的数值类型（不包括整型）：

IEEE754浮点数： float  （单精度） ， double  或 real  （双精度）
    定点数： decimal 或 numeric
    单精度浮点数的有效数字二进制是24位，按十进制来说，是8位；双精度浮点数的有效数字二进制是53位，按十进制来说，是16 位

一个实数的有效数字超过8位，用单精度浮点数来表示的话，就会产生误差！同样，如果一个实数的有效数字超过16位，用双精度浮点数来表示，也会产生误差

（3)    IEEE754标准的计算机浮点数，在内部是用二进制表示的，但在将一个十进制数转换为二进制浮点数时，也会造成误差，原因是不是所有的数都能转换成有限长度的二进制数。

即一个二进制可以准确转换成十进制，但一个带小数的十进制不一定能够准确地用二进制来表示。

四、建议使用INT UNSIGNED存储IPV4。

（1） 用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节。另外，计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储。

例如：

SELECT INET_ATON('209.207.224.40');

3520061480

SELECT INET_NTOA(3520061480);

209.207.224.40

五、 整形定义中不添加长度，比如使用INT，而不是INT(4)。

（1） mysql中int数据类型长度最大为11位,最少为4位,不够在前面补空格。

（2） 而mysql中int本身就是4个字节 bigint是8个字节 所以说int(X)的含义就是 int决定数据存储的字节 X表示期望数据的列宽度

在SQL语句中int代表你要创建字段的类型，int代表整型，11代表字段的长度。

整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，比如，不管设定了显示宽度是多少个字符，bigint都要占用8个字节。

六、短数据类型，使用TINYINT。

（1） 同样的字节数，非负存储的数值范围更大。如TINYINT有符号为 -128-127，无符号为0-255。

1、TINYINT ，字段类型，如果设置为UNSIGNED类型，只能存储从0到255的整数,不能用来储存[负数]。

2、TINYINT 型的字段如果不设置UNSIGNED类型,存储-128到127的整数。

提示：  通常，为了节省空间，应该尽可能的使用最小的 [整型数据]。一个TINYINT型数据只占用一个字节,一个INT型数据占用四个字节。这看起来似乎差别不大，但是在比较大的表中，字节数的增长是很快的。另一方面，一旦你已经创建了一个字段，要修改它是很困难的。因此，为安全起见，你应该预测一下，一个字段所需要存储的数值最大有可能是多大，然后选择适当的数据类型。

（2）

tinyint 1字节 （-128,127）

smallint 2字节 （-32768,32767）

int 无符号 0-65535

mediumint 3字节 （-8388608,8388607）

int或integer 4字节 （-2147483648,2147483647）

七、不建议使用ENUM类型，使用TINYINT来代替。

详细讲解：

说起这个ENUM， 经查阅各大技术社区的网络文摘，ENUM确实是mysql里的一个特色字段，印象里模糊记得在以前看到一些比较知名的商城系统如shopnc里面在用它，但也没细究，可能是因为他可以设置字段的区间范围，会让值可以被数据库所控制，有枚举约束的功能（比如，字段只想有0和1，如果用 TINYINT（1），结果就可能出现2，那2就是赃数据了）

但ENUM也有一些比较棘手的问题，比如数据迁移的时候，他几乎不可能被其他数据库所支持，如果enum里面是字符串，对于其他数据库来说就更郁闷了，还不能设为tinyint等类型的字段（enum虽然可以存储字符串，但对于内部来说，还是以顺序进行索引，比如'a','b','c'，我们也可以用索引值来获取值select * from tbl_name whre enum = 2，这与select * from tbl_name where enum = 'b'等义）如果你看明白了这两句SQL为什么等义，那么你也就可以了解为什么不主张用enum字段了。

也就是说，假如一个设计不合理的ENUM字段，给程序员带来的就完全是梦魇了，比如一个enum字段的范围是('0','1','2','3','4','5')，而enum的枚举值对应的索引是从1开始的，因此，insert into table (enum)values(1)，插入的并不是1，而是0。

另外假如你在设计好enum的枚举字段范围并使用了一段时间后，再到字段范围中加一个枚举值，并且不是加在最后，那么也就相当于把原来的范围都改变了索引值，也就是当你在查询的时候直接查询值（并加上单引号），将不会使用enum自身隐藏的索引值来获取结果了。

如果是纯数值型，还是建议采用tinyint字段吧，毕竟它也只占一个字节，即使出现赃数据，也可以被接受，不象enum，如果纯数字型范围，更改了索引，你就不知道你查询的值是否正确了）

八、尽可能不使用TEXT、BLOB类型。

1. TEXT、BLOB类型会使查询变慢，如果需要保存超长字符集，建议用varchar（n）类型或将过大字段拆分到其他表中；

2. 使用VARBINARY存储变长字符串，binary储存定长字符串。因为二进制字节流，不存在编码问题

    binary(n) ：固定长度为 n 字节，其中 n 值从 1 到 8,000 ，存储空间为 n 字节；
   
    varbinary( n | max)：可变长度，n 的取值范围为 1 至 8,000，max 是指最大存储空间是 2^31-1 个字节，即最大4GB；
   
    n：在表列定义或变量声明语句中没有指定 n，则默认长度为 1；在CAST 函数中没有指定 n，则默认长度为 30；
   
    详情：：：      [http://www.cnblogs.com/ljhdo/p/4530293.html](http://www.cnblogs.com/ljhdo/p/4530293.html)

   
   

对比

1、 BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种 BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

2、 BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

3、 在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHA

注意事项：

由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：

当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024；该值可以在启动d服务器时使用--max_sort_length选项进行更改。

运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值：

久、禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。

如果要存储图片、文件等 采用分布式文件系统更高效

十、VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。

区别：

1、char的总结：

char最大长度是255字符，注意是字符数和字符集没关系。可以有默认值，尾部有空格会被截断。

2、varchar的总结：

varchar的最大长度65535是指能存储的字节数，其实最多只能存储65532个字节，还有3个字节用于存储长度。注意是字节数这个和字符集有关系。一个汉字字符用utf8占用3字节，用gbk占用2字节。存储的最大字符数因编码不同而不同通常是n=65532/3或n=65532/2个字符。可以有默认值，尾部有空格不会截断。

二 理论知识

先说明一下 MySQL 历来版本对 varchar 的定义:

4.0版本以下，varchar(50)，指的是50字节，如果存放UTF8汉字时，只能存16个（每个中文3字节）

5.0版本以上，varchar(50)，指的是50字符，无论存放的是数字、字母还是UTF8中文（每个中文3字节），都可以存放50个

存储限制

需要额外占用字节存放字符的长度:小于255为1个字节，大于255则要2个字节

编码限制

gbk :每个字符最多占用2个字节

utf8:每个字符最多占用3个字节

utf8mb4 每个字符最多占用4个字节,中文占3个字节，emoji表情符号 占用4个字节

列长度限制

MySQL定义行的长度不能超过65535，该数值限制了列的数目。比如所有列为char(128) utf8字符集,最多有65535/(128*3)=170个列。

一、表字符集选择UTF8。

（1） 使用utf8字符集，如果是汉字，占3个字节，但ASCII码字符还是1个字节。

（2） 统一，不会有转换产生乱码风险

（3） 其他地区的用户（美国、印度、台湾）无需安装简体中文支持，就能正常看您的文字，并且不会出现乱码

（4） ISO-8859-1编码(latin1)使用了单字节内的所有空间，在支持ISO-8859-1的系统中传输和存储其他任何编码的字节流都不会被抛弃。即把其他任何编码的字节流当作ISO-8859-1编码看待都没有问题，保存的是原封不动的字节流。

十二、存储年使用YEAR类型。

**    重点：： mysql的日期与时间类型：分为time、date、datetime、timestamp、year，**

（1）、类型支持：year 与 year（4）,注意无year（2）的定义方式，否则报错“[Err] 1818 - Supports only YEAR or YEAR(4) column.”

create table if  not exists time(
atime  YEAR                         #year的定义，可写成year或者year(4)

) engine =innodb charset = utf8;

（2）、插入值,支持整数和字符串，支持 2位数 或者 4位数

00~69  将转换为2000~2069之间

70~99  将转换为1970~1999之间

#测试year类型insert into time values( 78);    #数据库中显示：1978
insert into time values('78'); #数据库中显示：1978

insert into time values('1978'); #数据库中显示：1978

（3）、注意点

1、 支持插入 数字0 或者 字符串0，实际显示的数值不同

insert into time  values( 0);  #数据库中显示：0
insert into time values('0'); #数据库中显示：2000

2、year只保存年份，占用空间小

3、其他和日期有关的可以通过整型保存

时间初  ： 存9位

十三、存储日期使用DATE类型。

MySQL日期类型、日期格式、存储空间、日期范围比较。

日期类型        存储空间       日期格式                 日期范围

------------ ---------   --------------------- -----------------------------------------

datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038

date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31

year           1 bytes   YYYY                  1901                ~ 2155

十四、存储时间（精确到秒）建议使用int/bigint类型，int使用4字节，bigint使用8个字节。

1）int (1) 4个字节存储，INT的长度是4个字节，存储空间上比datatime少，int索引存储空间也相对较小，排序和查询效率相对较高一点点 （2）可读性极差，无法直观的看到数据，可能让你很恼火

可以略微注意2038年问题的陷阱。对于MySQL而言，如果存时间戳请使用timestamp或bigint，而不要使用int。 2)TIMESTAMP

（1）4个字节储存 （2）值以UTC格式保存 （3）时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。 （4）TIMESTAMP值不能早于1970或晚于2037

3)datetime （1）8个字节储存 （2）与时区无关 （3）以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'a

十五、建议字段定义为NOT NULL。

**   （1）**空值是不占用空间的， not null的效率比null高

** （2） ** MySQL中的NULL其实是占用空间的  ： 打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。

十六、表结构变更需要通知DBA审核。

数据库管理员 DBA ：：：Database Administrator

**    （1）** 每次变更不能说变就变了，不然，别人不知道，肯定也是按照原来的来，报错的话，也就不好往下进行了，

看完上述内容，你们对Mysql中有哪些建表规范有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。