目录一、MySQL建表语句二、MySQL建表字符串类型设计1、CHAR2、VARCHAR3、枚举类型设计实战三、MySQL建表ID和金额的设计与实战1、ID自增的设计2、互联网企业金额字段设计原理四、...

一、mysql建表语句
二、MySQL建表字符串类型设计
三、MySQL建表ID和金额的设计与实战
2、互联网企业金额字段设计原理
四、MySQL建表时间类型设计与实战
五、MySQL高扩展jsON设计与实战

本文作为MySQL系列第三篇文章，详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱，对网络上常见的资料给出的设计方案，做了博主自己的理解和反驳。

一、MySQL建表语句

MySQL建表语句很简单，CREATE TABLE 表名 ()，在其中设置表的列（属性）即可。

二、MySQL建表字符串类型设计

CHAR(N) 用来保存固定长度的字符（在Unicode字符集，Utf-8、Utf-16、Utf-32是这样的），N 的范围是 0 ~ 255，请牢记，N 表示的是字符，而不是字节。

在表结构设计中还需要额外定义建表对应的字符集。多字节字符集 (MBCS)，通常指的是ANSI、中文编码以及Shift-jis，jis，euc-jp，euc-kr等。Unicode字符集，Unicode字符集即平常说的宽字节，包含Utf-8、Utf-16、Utf-32。

常见的字符集有 GBK、UTF8，通常推荐把默认字符集设置为 UTF8。

VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符。在超出 65536 个字符的情况下，可以考虑使用更大的字符类型 TEXT 或 BLOB，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。

随着移动互联网的飞速发展，推荐把 MySQL 的默认字符集设置为 UTF8MB4，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。

鉴于目前默认字符集推荐设置为 UTF8MB4，所以在表结构js设计时，可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样。

设计表结构时，你会遇到一些固定选项值的字段。例如状态字段（***_state），有效的值为有限状态，例如01（订单初始状态）、02（下单成功）、03（支付中）……。

很多学习资料和博客推荐在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚举类型，只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式，插入非定义数据就会报错。

这里博主要跟这些资料唱个反调，我们在工程中的状态，基本都是我们手动set的，这里博主认为如果使用了 ENUM 字符串枚举类型恰恰不利于互联网的高速扩展的设计原则。

在这里我推荐在工程中维护一个 ENUM 枚举类，我们对数据库操作的的时候状态或者相关枚举类型的字段从枚举类中获取，这样方便维护，并且利于扩展。

三、MySQL建表ID和金额的设计与实战

进行实战设计之前，我们需要了解整型类型，

除了整型类型，数字类型还有浮点和高精度类型。MySQL 之前的版本中存在浮点类型 Float 和 Double，在真实的生产环境中不推荐使用，在计算时由于精度类型问题，会导致最终的计算结果出错。

ID一般我们会设置为自增，结合 auto_increment，可以jVwkM实现自增功能，但在表结构设计时用自增做主键一般只会使用 BIGINT 类型做主键。

原因有几点一是为了扩展性，int 的取值范围不一定适用互联网场景的增速，这里面需要注意MySQL 8.0 版本前自增不持久化，自增值可能会存在回溯问题，例如 1/2/3/4，我把4删点，再次插入的时候，主键ID还是 1/2/3/4，这就是回溯问题，解决办法就是在使用的时候评估这个方案会不会有影响，或者直接升级MySQL。

2、互联网企业金额字段设计原理

我们常常在其他博客看到这样一种说法“在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型 BIGINT类型。”，他给出的理由是所有金额相关字段都是定长字段，占用 8 个字节，存储高效。第二直接通过整型计算，效率更高。

而事实上真的是这样吗？

金额字段的取值范围如果用 DECIMAL 表示的，则定义为 DECIMAL(16,2) ，这样满足的万亿以上的场景了。

为什么我推荐使用DECIMAL而不是BIGINT，我们在存储金额的时候一般是分为单位，例如100,.00就是 1 元，当我们下单金额例如100元，我们的库里就会落 10000.00，但是这比订单购买了1个item商品3件sku，这100元就要分摊给这3件sku商品，这时候对于分摊的计算，在代码中int、long类型没有BigDecimal 计算的精准。

四、MySQL建表时间类型设计与实战

因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常见使用的日期类型为DATETIME 和 TIMESTAMP。

4 个字节，因此其存储的时间上限只能到‘ 03:14:07’。

从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。同时，DATETIME 不存在时区转化问题。一般是在国际化项目中，服务器端或者前端进行转换，这样查询或者变更效率更高。

每个表都要有一个时间字段， 在做表结构设计规范时，强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段，并设置修改自动更新机制， 即便标识每条记录最后修改的时间。开发人员可以知道每次操作记录更新的时间，以便做后续的处理。

比如在电商的订单表中，可以方便对支付超时的订单做处理；在金融业务中，可以根据用户资金最后的修改时间做相应的资金轧差等。

五、MySQL高扩展JSON设计与实战

关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能。

这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。JSON 类型的另一个好处是无须预定义字段，字段可以无限扩展。

但是这里，博主并不推荐大家这么做，因为JSON类型及其难维护，并且写sql的时候很麻烦

我举个例子，我想插入一条信息，我需要

一般在生产中我们这样处理，在定义时，定义一个超大的字符串类型，在代码中使用JSON转换成一个JSON对象的字符串，保存。

本文作为MySQL系列第三篇文章，详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱，对网络上常见的资料给出的设计方案，做了博主自己的理解和反驳。

到此这篇关于MySQL中建表与常见的类型设计陷阱详解的文章就介绍到这了,更多相关MySQL建表 类型设计内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

　　篇一：SQL学习教程

　　SQL 简介SQL 是用于访问和处理数据库的标准的计算机语言。什么是 SQL？? SQL 指结构化查询语言 ? SQL 使我们有能力访问数据库 ? SQL 是一种 ANSI 的标准计算机语言编者注：ANSI，美国国家标准化组织SQL 能做什么？? SQL 面向数据库执行查询 ? SQL 可从数据库取回数据 ? SQL 可在数据库中插入新的纪录 ? SQL 可更新数据库中的数据 ? SQL 可从数据库删除记录 ? SQL 可创建新数据库 ? SQL 可在数据库中创建新表 ? SQL 可在数据库中创建存储过程 ? SQL 可在数据库中创建视图 ? SQL 可以设置表、存储过程和视图的权限SQL 是一种标准 - 但是...SQL 是一门 ANSI 的标准计算机语言， 用来访问和操作数据库系统。 SQL 语句用于取回和更新数据库中 的数据。 SQL

　　Carter 在本教程中，我们将为您讲解各种不同的 SQL 语句。重要事项一定要记住，SQL 对大小写不敏感！SQL 语句后面的分号？某些数据库系统要求在每条 SQL 命令的末端使用分号。在我们的教程中不使用分号。 分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以 上的语句。 如果您使用的是 MS Access 和 SQL Server 2000，则不必在每条 SQL 语句之后使用分号，不过某些 数据库软件要求必须使用分号。SQL DML 和 DDL可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。 SQL (结构化查询语言)是用于执行查询的语法。 但是 SQL 语言也包含用于更新、 插入和删除记录的语法。 查询和更新指令构成了 SQL 的 DML 部分：? SELECT - 从数据库表中获取数据 ? UPDATE - 更新数据库表中的数据 ? DELETE - 从数据库表中删除数据 ? INSERT INTO - 向数据库表中插入数据SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之 间的链接，以及施加表间的约束。 SQL 中最重要的 DDL 语句:? CREATE DATABASE - 创建新数据库 ?

　　篇二：如何学习SQL

　　在论坛中不断看到有新人问一些基础的问题。于是抽空整理了本帖，希望对新人有所帮助。

　　本文无意取代任何一本数据库或SQL的参考书，主要是提供一点学习方向的指导和技术心得的分享。地图和指南针并不能代替其它的野营工具，经验和心得也只能来自于亲身走过的旅途。

　　本文主要以SQL Server 2005的T-SQL为示例，但原理并不局限于这一平台。

　　由于全文过长，故拆分成四部分发帖：

　　第一部分：SQL基础

　　1. 为什么学习SQL

　　2. 学习SQL的参考资料

　　3. 几组基本概念

　　3.1. 单机数据库与服务器级数据库

　　3.5. 语句、表达式和断言

　　4. SQL不同于一般编程语言的地方

　　4.2. SQL是基于集合的说明式语言

　　5. SQL的三个子集

　　第二部分：从关系角度理解SQL

　　6. 从关系角度理解SQL

　　6.1. 关系和表

　　6.2. 关系模型

　　6.3. 关系运算

　　6.6. 表的逻辑含义

　　第三部分：SQL数据类型与三值逻辑

　　第四部分：DBMS扩展功能与SQL高级话题

　　9. DBMS提供的扩展功能

　　9.2. 动态语句

　　9.3. DBMS支持的数据库对象

　　9.4. DBMS提供的系统函数、系统视图和系统存储过程

　　第一部分：SQL基础

　　1. 为什么学习SQL

　　自人类社会形成之日起，社会的运转就在不断地产生和使用各种信息（文献、档案、资料、数据等）；在如今所谓的信息时代，由于计算机和互联网的作用，信息的产生和使用达到前所未有的广度和深度。如何管好和用好信息，是（而且将一直是）IT行业一块重要的领域。 在过去几十年中，关系数据库一直在这一领域占主导地位，而建立在关系理论基础之上的SQL也成为数据库领域的既定标准。

　　目前的数据存储领域可称为三分天下：

　　a. 少量数据的存储：

　　自定义数据文件或通用数据文件（单机数据库），通过自定义接口或通用API访问数据。如需要存储数据的单机软件或小型的动态网站。

　　b. 对一致性要求高的大量数据的存储：

　　关系数据库管理系统(RDBMS)。如各种传统的信息系统（ERP、CRM、HRMS、MIS等）。

　　c. 对并发性要求高的大量数据的存储：

　　NoSQL数据库系统。如Web2.0网站的后台数据系统。

　　以上，“狐假虎威”地借数据存储的重要性来阐述了一番学习SQL的伟大意义。但重要的是，对SQL善于做什么和不善于做什么有个清楚的认识。

　　2. 学习SQL的参考资料

　　不久前整理了数据库图书ABC一文，对数据库相关的参考资料作了粗略的分类。

　　对于初学者而言，可以结合着《数据库系统概念(第5版)》一书和某个DBMS平台的入门技术手册练习，自行寻找或构思一个小需求，建一个数据库，创建几个表和视图，练习写查询和修改语句。基础理论和技术实践可以相互促进。

　　3. 几组基本概念

　　3.1. 单机数据库与服务器级数据库

　　单机数据库（如sqlite、Access等，Excel也勉强可以算是）是应用于单个计算机的数据库引擎，通常不具备网络连接功能，适用于小型应用；程序部署时，一般只需要附带数据文件即可。有时也称作桌面数据库。

　　服务器级数据库（如Oracle、DB2、SQL Server、MySQL、PostgreSQL等）是具备网络连接功能、可作为单独数据库服务器的DBMS，适用于大型信息系统；程序部署时，需要专门安装相应的DBMS，甚至要单独进行数据库服务器的架构设计。此类数据库是我们讨论的重点。

　　数据库服务器是运行在一台主机(Host)（或主机集群）上的服务程序，维护着一个或多个数据库，并通过网络连接响应数据库客户端提交的SQL语句。

　　数据库客户端是向数据库服务器发送查询请求的应用程序，可能是DBMS的GUI管理界面或命令行应用程序，也可能是前端的Web服务器。数据库客户端和数据库服务器可能是在同一台主机上，但更多情况下则是位于不同的主机上，通过局域网访问。

　　例如对于SQL Server来说，一个服务器实例(Instance)即是一个数据库服务器，一台主机上可以安装多个服务器实例；而查询分析器或SSMS、sqlcmd、以及连接数据库服务器的IIS，都是数据库客户端。

　　比如你在SSMS中备份/还原/附加一个数据库，或是通过xp_cmdshell执行一个命令程序，所操作的都是你所连接的数据库服务器所在主机的文件，而不是你运行SSMS数据库客户端所在主机的文件。

　　一台主机有时候会被称作一台（操作系统）服务器，而数据库服务器和Web服务器都是运行在主机之上的应用服务器。它们都被称作服务器，不要因此搞混了。

　　一个典型的基于SQL Server的网站系统的架构示例如下：

　　“数据库”这个词已经被滥用，可能用来指一个数据系统（如中国移动的号码数据库），可能用来指一种数据存储技术（如关系数据库和NoSQL数据库），还可能用来指DBMS(如人们常说SQL Server是数据库软件）。这种混乱已然形成，恐怕难以改变（比如上文的描述即是如此）。我们只能根据上下文来判断具体含义。

　　按最狭义的技术含义，数据库(Database)是指位于一个数据库服务器实例上的一个库，而DBMS则是指类似SQL Server、Oracle等等此类软件。初学者要注意这些概念之间的差别。论坛上常常见到这样的帖子：“连不上数据库”、“数据库打不开了”，又没有上下文，可见发问者概念混乱，搞得解答者也是一头雾水。

　　SQL是一个关系数据库查询语言的标准，而SQL方言则是各种DBMS在SQL标准上进行的扩展，如增加新的关键字、查询功能、特有的数据类型、支持过程化的控制流语句等。例如SQL Server的T-SQL和Oracle的PL/SQL都是常见的SQL方言。

　　这就好比ANSI C标准与各种编译器实现的C语言的差别。但不同SQL方言之间的差异远大于不同C编译器之间的差异。SQL方言之间的差异，对于跨DBMS的学习和开发，都是必须注意的。

　　3.5. 语句、表达式和断言

　　语句(statement)是SQL中一个可以单独执行的单元。如SELECT * FROM table;即是一个语句，其中包含了SELECT子句(clause)和FROM子句。SQL标准规定用分号作为语句的结束，但在目前的T-SQL中，语句结束的分号是可选的。

　　表达式(expression)是SQL中的一个值（可能是变量、常量、查询字段或计算结果），对应一种特定的数据类型。SQL中的表达式分为标量表达式和表值表达式，其中表值表达式作为单独语句则是SELECT语句，作为语句的一部分则称为子查询。比如0, col + 2, DATEADD(second, 30, GETDATE())都是（标量）表达式。

　　需要特别说明的是，SQL中的CASE WHEN是标题表达式，而不是条件语句。比如CASE WHEN中可以使用表达式，却不能使用语句；CASE WHEN的结果是一个特定数据类型的标量值；CASE WHEN可以用在SELECT、GROUP BY或ORDER BY子句中，但IF ELSE则不行。

　　是SQL中进行比较的结果，即真值，可理解为布尔表达式，因为SQL中没有bool数据类型，所以将断言特别从表达式中区分出来。比如1是一个标量表达式，而1 = 1则是一个断言，后者可以用在WHERE、ON、HAVING、CHECK等需要真值条件的地方，但前者则不可以。由于NULL的存在，SQL中的断言是三值逻辑，即True/False/Unknown，详见下文“NULL与三值逻辑”。

　　下文中多次用到计算和比较两个词。表达式和表达式进行计算，结果是新的表达式；表达式和表达式进行比较，结果是一个断言；断言和断言可以进行逻辑运行(AND/OR/NOT)，结果是新的断言。注意其中的区别。

　　4. SQL不同于一般编程语言的地方

　　SQL是数据库的查询语言，因而可以对系统数据产生持久化影响。在常规编程中，一个错误通常只会造成程序的crash或bug，修改并重新调试往往就可以了；而在SQL中，一个不小心就可能造成系统数据的破坏和丢失。常常有新手执行SQL时，不小心遗漏了DELETE或UPDATE语句中的WHERE子句，这往往是很大的麻烦。

　　SQL Server 2008数据库―创建、建表、查询语句

　　1、利用对象资源管理器创建用户数据库：

　　（3）展开SQL Server 实例，右击“数据库”，然后人弹出的快捷菜单中选择“新建数据库存”命令，打开“新建数据库”对话框。

　　（4）在“新建数据库”对话框中，可以定义数据库的名称、数据库的所有者、是否使用全文索引、数据文件和日志文件的逻辑名称和路径、文件组、初始大小和增长方式等。输入数据库名称student。

　　1、利用表设计器创建数据表：

　　（2）展开SQL Server实例，选择“数据库”―student―“表”，单击鼠标右键，然后从弹出的快捷菜单中选择“新建表”命令，打开“表设计器”。

　　（3）在“表设计器”中，可以定义各列的名称、数据类型、长度、是否允许为空等属性。

　　（4）当完成新建表的各个列的属性设置后，单击工具栏上的“保存”按钮，弹出“选择名称”对话框，输入新建表名stu_info，SQL Server数据库引擎会依据用户的设置完成新表的创建。

　　2、利用SQL语句创建数据表：

　　3、样本数据库student表数据：

　　学生信息表（stu_info）

　　据库系统的基本结构 在写我的第一篇笔记之前，先废话几句，最近打算学习一段时间的数据库知识并打算把我的读书心得晾出来和大家分享一下，希望感兴趣的园友能能够一起探讨，共同提高。有理解的不对的地方也欢迎各位大牛拍砖，这年头不为别的，就为了能掌握点真理！

　　废话说完了，开始今天的正文。

　　首先来上一张图片，了解一下数据库系统的基本结构

　　下面我想用一个简单的比喻来描述一下数据库系统的基本结构。

　　数据库系统=====》仓储中心

　　数据库========》仓储中心的货场或者是仓库

　　数据=========》存储在货场或者是仓库中的货物

　　数据库管理系统===》在仓储中心一系列的管理体制规则下的仓库自动化管理系统

　　数据库管理员====》仓库的库管员

　　应用系统======》仓储中心对外的服务窗口

　　操作系统======》公司或者集团，是一个基础环境的作用

　　这样就可以将数据库系统的运行描述为一个仓储中心的运转。

　　当有客户（用户）来仓储中心（数据库系统）存/取货物的时候，客户（用户）首先要接触的是仓储中心的对外服务窗口（软件系统），服务窗口会对客户（用户）的身份进行验证，出示证件或其他的一些方式和方法。客户（用户）身份验证通过之后就可以进行通过服务窗口索取服务（发出一些命令）来传给仓库的自动化管理系统（DBMS）会对这些命令再一次进行验证，验证通过就提供相应的服务（返回相应的结果），验证失败会拒绝服务（返回一个错误）。在这个过程中，客户（用户）不需要了解你想要的`货物放在仓库（数据库）的具体位置，这么多的货物（数据）究竟是怎样的一个组织结构。同样的一个仓库或者是货场怎么才能更有效率的想客户（用户）提供服务，怎样才能存放更多的货物，这些都是仓库管理员（数据库管理员）应该做的事情。

　　关于数据库系统的几点说明

　　1、（数据库）DB和（数据库管理系统）DBMS：数据库和数据库管理系统是两个不同的概念。数据库是数据存储的仓库，是一个实体，能够合理的存放数据的地方。数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。我们通常说的oracle、sql server、mysql、db2属于DBMS的范畴。

　　2、数据库系统中数据的模型：层次模型、网状模型、关系模型。我们通常所说的关系型数据库就是根据数据模型来划分的。

　　3、数据库管理系统是数据库系统的核心，对数据库的一切操作，如原始数据的装入、检索、更新、再组织等等，都是在DBMS的指挥、调度下进行的，它是用户与物理数据库之间的桥梁，根据用户的命令对数据库执行必要的操作。

　　4、由于数据库管理系统（DBMS）的核心作用，所以我们学习数据库的时候主要还是学习的是数据库管理系统（DBMS）。

　　5、在实际的使用过程中我们一般不关心数据的物理结构只关心数据的逻辑结构，这就得借助于功能强大的数据库管理系统（DBMS）

　　数据库 关系数据库（Relational Database ，RDB）基于关系模型的数据库。是现代最流行的数据管理系统中应用最为普遍的一种，也是最有效率的数据组织方式之一。

　　理解关系数据库可以从两方面进行理解：

　　1、关系数据库是有由行与列构成的二维表表之间的关联组成

　　2、表的关联。表的关联的好处就是无需将相同的数据重复的进行存储，降低了数据的冗余度。 术语

　　键码（key）：在关系中用来标识行的一列或者是多列

　　主关键字（Primary Key）：是表行的唯一标识的候选关键字。这里需要注意的地方是，一个表只有一个关键字；主关键字可以由一个或者是多个字段组成，分别称为单段主键和多段主键

　　候选关键字（Candidate Key）：唯一标识表中的一行而又不包含多余属性的一个属性集 公共关键字（Common Key）：两个数据表中具有相同或者是相容的属性或者是属性组，那么这个属性或属性组就称之为关系的公共关键字

　　外关键字（Foreign Key）：外关键字存在于公共关键字的基础之上。在公共关键字中如果这个公共关键字在其中的一个数据表中是主关键字那么这个公共关键字就称之为另一个表的外关键字。

　　目前关系数据库有六种范式，而在实际设计数据库的时候，通常用到的是前三种范式

　　a)数组的每个属性只能包含一个值

　　b)关系中的每个数组必须包含相同数量的值

　　c)关系中每一个数组一定不能相同

　　如果符合1NF则修改如下

　　2NF 满足第一范式的基础上，数据表中的任何一个非主键字段的数据都依赖于该数据表中的主关键字。

　　若满足2NF则需要将上面的表分拆为一下两个表：

　　在满足第二范式的基础上，满足第三范式的条件是数据表中的任何两个非关键字段的数据值之间不存在函数依赖关系。

　　这个范式比较容易理解就不拿例子来解释了。

　　这样做有什么好处吗？答案是肯定的哈。没有无缘无故的爱和恨，呵呵。

　　2、避免的数据变动时发生人为的错误

　　怎么样理解这样的一个概念呢。通过一个简单的例子来解释这样的一个概念。

【关于SQL学习教程】相关文章：