1分钟带你搞懂 mysql 表结构设计与优化

数据库设计字段格式

1.字段大小 ：对应问题 “ Out of range value for column ‘字段名’ ”

基本长度：

	字节	最小值	最大值
tinyint	1	有符号 -128 ；无符号 0	有符号 127 ；无符号 255
smallint	2	有符号 -32768；无符号 0	有符号 32768 ；无符号 65535
mediumint	3	有符号 -8388608；无符号 0	有符号 8388607；无符号 1677215
int、integer	4	有符号 -2147483648 ；无符号 0	有符号 2147483647 ；无符号 4294967295
bigint	8	有符号 -9223372036854775808；无符号 0	有符号 -9223372036854775807；无符号 18446744073709551615
float	4	±1.175494351E-38	±3.402823466E+38
double	8	±2.2250738585072014E-308	±1.7976931348623157E+308
dec(M,D),decimal(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定
bit	1-8	bit(1)	bit(8)

数据库存入的数据的大小由上表数据类型中基本大小决定：

ps:设置表中长度的作用

作用：如果不指定长度，数据库在存储的时候都必须给每个字段预留最大的存储空间，这样极大的浪费了空间，也加大了数据库本身管理的难度。字段类型长度应设置为保证正常使用需求下的最小长度

但是这个表中的长度对于上表中的数据其实是没啥作用的，

例如：下面的三种情况

reason字段长度给定了 4个字节数 ，类型为tinyint 范围是 0 - 255 ，所以 当我设置插入reason为 999的时候就会报错

  Out of range value for column '字段名'  

因为虽然 999 是三个字符 但是范围超过了255。

reason字段长度给定了 4个字节数 ，类型为tinyint 范围是 0 - 255 ，所以 当我设置插入reason为 99的时候就不会报错

（会插入 0099 不过现在NaviCat 给优化了看到的是99）

reason字段长度给定了 1个字节数 ，类型为tinyint 范围是 0 - 255 ，所以 当我设置插入reason为 99的时候也不会报错

（会插入 99 ）

那肯定会好学的小盆友会问那这个限制长度就光是不补零的作用吗，那肯定不只是这个作用，

当为varchar字符类型的时候，这里的长度是指字符的个数。

也就是说如果这里把类型修改为CHAR（3） 长度为3，那么就只能输 999 。输入9999会报错，输99不报错，剩下的两个字符用空格填充。

2.索引效率问题

像reason这样的字段本身储存的就是1，2，3这种数据，那么我可以 用 tinyint（3） ，也可以用 int （3），也可以用varchar（3）最好应该用什么呢

1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。

（为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。）

3、TEXT。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。一般用于存储文章，备注啊等等

4、NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar 数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

3.储存效率

搞这么多花里胡哨的东西肯定是为了花最小的空间存最多的东西辣，当然就像收拾东西，当你存储的多了，你找起来会麻烦，所以实际应用上还是要好好取舍一下

char、varchar、text、ntext、bigint、int、smallint、tinyint和bit的区别及数据库的数据类型

Varchar 对每个英文(ASCII)字符都占用2个字节，对一个汉字也只占用两个字节

char 对英文(ASCII)字符占用1个字节，对一个汉字占用2个字节Varchar 的类型不以空格填满，比如varchar(100)，但它的值只是"qian",则它的值就是"qian"而char 不一样，比如char(100),它的值是"qian"，而实际上它在数据库中是"qian "(qian后共有96个空格，就是把它填满为100个字节)。由于char是以固定长度的，所以它的速度会比varchar快得多!但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉!

ntext

可变长度 Unicode 数据的最大长度为 230 - 1 (1,073,741,823) 个字符。存储大小是所输入字符个数的两倍（以字节为单位）。ntext 在 SQL-92 中的同义词是 national text。

text

服务器代码页中的可变长度非 Unicode 数据的最大长度为 231-1 (2,147,483,647) 个字符。当服务器代码页使用双字节字符时，存储量仍是 2,147,483,647 字节。存储大小可能小于 2,147,483,647 字节（取决于字符串）。

bigint：从-2^{63(-9223372036854775808)到2}63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。

int：从-2^{31(-2,147,483,648)到2}31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。

smallint：从-2^{15(-32,768)到2}15-1(32,767)的整数数据，存储大小为 2 个字节。

tinyint：从0到255的整数数据，存储大小为 1 字节。

bit：1或0的整数数据，存储大小为 1 字节。

其中常用的数字型所占用的字节数如下，根据字节数即可算出表示的范围了
TINYINT 1 字节
SMALLINT 2 个字节
MEDIUMINT 3 个字节
INT 4 个字节
INTEGER 4 个字节
BIGINT 8 个字节
FLOAT(X) 4 如果 X < = 24 或 8 如果 25 < = X < = 53
FLOAT 4 个字节
DOUBLE 8 个字节
DOUBLE PRECISION 8 个字节
REAL 8 个字节
DECIMAL(M,D) M字节(D+2 , 如果M < D)
NUMERIC(M,D) M字节(D+2 , 如果M < D)

日期和时间类型
列类型 需要的存储量
DATE 3 个字节
DATETIME 8 个字节
TIMESTAMP 4 个字节
TIME 3 个字节
YEAR 1 字节

串类型
列类型 需要的存储量
CHAR(M) M字节，1 VARCHAR(M) L+1 字节, 在此L TINYBLOB, TINYTEXT L+1 字节, 在此L< 2 ^ 8
BLOB, TEXT L+2 字节, 在此L< 2 ^ 16
MEDIUMBLOB, MEDIUMTEXT L+3 字节, 在此L< 2 ^ 24
LONGBLOB, LONGTEXT L+4 字节, 在此L< 2 ^ 32
ENUM(‘value1′,’value2′,…) 1 或 2 个字节, 取决于枚举值的数目(最大值65535）
SET(‘value1′,’value2′,…) 1，2，3，4或8个字节, 取决于集合成员的数量(最多64个成员）

该博客引用博文

https://blog.csdn.net/weixin_42328375/article/details/104639365
https://blog.csdn.net/qq_39597203/article/details/80024544
https://www.cnblogs.com/feng9exe/p/11393159.html