mysql基本数据类型

  1. 数值型

    • 整数

    • 小数

      • 定点数（建议使用这个，不存在精度损失，如货币运算）
        decimal（m,d） 指定整数位与小数位长度的小数类型，8字节
        m代表整数位数和小数位数的总长度，d代表小数位数的长度
        如果超出则插入临界值，例如整数位数指定两位，插入100的话，它就会自动插入99
      • 浮点数（会丢失精度）
        float/double，4字节/8字节

  2. 字符型

    • 较短的文本
      • char(10)
        括号中指定最长字符数，
        char是定长的，例如指定长度是10，只写入两个字符，依然占据10个字符的长度
      • varchar(10)
        长度是可变的，例如指定长度是10，只写入两个字符，只会占据两个字符的长度
    • 较长的文本：text,blob（较长的二进制数据）

char最大长度为255，varchar则大很多。

我们可以预见数据的长度，就用char。
char的效率要比varchar高，因为varchar是弹性的，空间。

  1. 日期型
    • date，日期，4字节
    • datetime，时间，8字节
    • timestamp 时间戳，4字节

关系型数据库设计：三大范式的通俗理解

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

举例说明：
在这里插入图片描述
在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：
在这里插入图片描述
可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

在这里插入图片描述
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：
在这里插入图片描述
在这里插入图片描述

第三范式（3NF）：在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：
在这里插入图片描述
上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

在这里插入图片描述
在这里插入图片描述
这样一来，就满足了第三范式的要求。

ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

反范式

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。
例如：在上例中的 user_role用户-角色中间表增加字段role_name。
反范式化可以减少表的关联查询（join），多表联查还有可能会引起索引失效，所以在有些场景使用反范式是会极大提高查询效率的。

具体的场景还没找到，找到再来补充

mysql表设计

主键设计问题

为什么一定要设一个主键？

因为你不设主键的情况下，innodb也会帮你生成一个隐藏列，作为自增主键。所以啦，反正都要生成一个主键，那你还不如自己指定一个主键，在有些情况下，就能显式的用上主键索引，提高查询效率！

主键为什么不推荐有业务含义?

因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。

主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。

时间字段用什么类型?

回答:此题无固定答案，应结合自己项目背景来答！把理由讲清楚就行！

一般会使用datetime或者timestamp这两种类型存储。

  1. varchar，如果用varchar类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为2013111的数据，请问这是代表2013年1月11日，还是2013年11月1日？

    其次，做时间比较运算，你需要用STR_TO_DATE等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，会影响效率，是个坑！

    在 存储的时间将来不需要进行大量计算的前提下，可以考虑选择varchar类型，反之，选择datetime类型。

  2. timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07；存入是整型，显示是日期格式。
    2038年以后的时间，是无法用timestamp类型存储的。
    但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区

SET TIME_ZONE = "america/new_york";

你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

  1. datetime，datetime储存占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。
    但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

  2. bigint，也是8个字节，自己维护一个时间戳，表示范围比timestamp大多了，就是要自己维护，不大方便。

为什么不使用字符串存储日期？
字符串无法完成数据库内部的范围筛选

如果需要使用时间戳timestamp和int该如何选择？
在这里插入图片描述

货币字段用什么类型?

回答:如果货币单位是分，可以用Int类型。如果坚持用元，用Decimal。
千万不要答float和double，因为float和double是以二进制存储的，所以有一定的误差。
打个比方，你建一个列如下

CREATE TABLE `t` (
  `price` float(10,2) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后insert给price列一个数据为1234567.23，你会发现显示出来的数据变为1234567.25，精度失准！

为什么不直接存储图片、音频、视频等大容量内容?

回答:我们在实际应用中，都是用HDFS来存储文件。然后mysql中，只存文件的存放路径。mysql中有两个字段类型被用来设计存放大容量文件，也就是text和blob类型。但是，我们在生产中，基本不用这两个类型！

主要原因有如下两点

  1. Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
  2. binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！

因此，不推荐使用text和blob类型！

字段为什么要定义为NOT NULL?

  1. 索引性能不好
  2. 查询会出现一些不可预料的结果
create table table_2 (
	 `id` INT (11) NOT NULL,
	name varchar(20) NOT NULL
)

# 表数据为
id name
1  zs
2
3
4 ls


select count(name) from table_2;
# 你会发现结果为2，但是实际上是有四条数据的！类似的查询问题，其实有很多，不一一列举。
# 记住，因为null列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查Bug.

根据MySQL的官网文档及《mysql技术内幕-存储引擎》和《高性能Mysql》一说，MySQL中存储null的确为额外占用一些空间，但将这些列从null优化为not null，并非会有很大的性能提升。

null的问题主要还是一些逻辑方面的问题，比如可能会使计算复杂些，会让我们处理的时候要多考虑些，亦或者是和其他中间件进行结合的时候，其他中间件对null的处理不太友好而出现一些问题等。

char、varchar和text的设计

最近有表结构设计中出现了varchar（10000）的设计引起了大家的讨论，我们下面就来分析分析。

char、varchar和text基础知识

  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不会。

总体来说

  • char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。
  • varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。
  • text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，故可以全部使用65535。

结构设计中出现了varchar（10000）用什么类型？

当varchar（n）后面的n非常大的时候我们是使用varchar好，还是text好呢？这是个明显的量变引发质变的问题。我们从2个方面考虑，第一是空间，第二是性能。

  1. 首先从空间方面：

    从官方文档中我们可以得知当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：

    • 大于varchar（255）变为 tinytext
    • 大于varchar（500）变为 text
    • 大于varchar（20000）变为 mediumtext

所以对于过大的内容使用varchar和text没有太多区别。

  1. 其次从性能方面：

    索引会是影响性能的最关键因素，而对于text来说，只能添加前缀索引，并且前缀索引最大只能达到1000字节。

    而貌似varhcar可以添加全部索引，但是经过测试，其实也不是。由于会进行内部的转换，所以long varchar其实也只能添加1000字节的索引，如果超长了会自动截断。

所以我们认为当超过255的长度之后，使用varchar和text没有本质区别，只需要考虑一下两个类型的特性即可。（主要考虑text没有默认值的问题）

但是个人推荐使用varchar（10000），毕竟这个还有截断，可以保证字段的最大值可控，如果使用text那么如果code有漏洞很有可能就写入数据库一个很大的内容，会造成风险。

故，本着short is better原则，还是使用varchar根据需求来限制最大上限最好。

数据库字段用户自己定义，怎么设计

思路就像es一样，使用json，暂时这么答

PUT /my_index
{
  "mappings": {
    "properties": {
      "age":{
        "type": "integer"
      },
      "email":{
        "type": "keyword"
      },
      "name":{
        "type": "text"
      }
    }
  }
}

mysql什么时候触发表锁，什么时候触发行锁

行锁是一种细粒度的锁，它只锁定表中的某一行数据。行锁在以下情况下会被触发：

  1. 事务对某一行数据进行更新：当一个事务对某一行数据进行更新操作时，MySQL会自动获取该行的行锁。这样可以防止其他事务并发地修改同一行数据，保证数据的一致性。

  2. 事务对某一行数据进行读取：当一个事务对某一行数据进行读取操作时，如果使用的是InnoDB存储引擎，MySQL会自动获取该行的行锁。这样可以防止其他事务并发地修改或删除同一行数据，保证读取的数据的一致性。

  3. 需要注意的是，行锁只对使用InnoDB存储引擎的表有效。而使用其他存储引擎如MyISAM的表只支持表级锁。

总结起来，MySQL中的表锁和行锁在不同的情况下会被触发。表锁在使用ALTER TABLE语句修改表结构、LOCK TABLES语句显式锁定表以及使用MyISAM存储引擎时会被触发。行锁则在事务对某一行数据进行更新或读取时会被触发。了解这些锁的触发情况，可以帮助我们更好地设计和优化数据库应用程序，提高并发性能和数据一致性。

部分内容引用自：
https://www.cnblogs.com/billyxp/p/3548540.html
https://www.cnblogs.com/wsg25/p/9615100.html
https://www.cnblogs.com/youngdeng/p/12855570.html
https://baijiahao.baidu.com/s?id=1706684904063421331&wfr=spider&for=pc
https://developer.aliyun.com/article/1275371

如果有人祈求佛祖保佑另一个主，那就只有一种可能，她的那个主是真是存在的。

《三体》刘慈欣

# 数据库 # mysql # sql
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

cover

KingbaseES数据库：医疗信创潮涌沅江，国产化信创打造医疗新质生产力

avatar DAMO开发者矩阵
cover

告别迁移焦虑：金仓KDMS V4带你轻松搞定数据库国产化替代

avatar DAMO开发者矩阵
cover

KingbaseES数据库在常德二院全栈国产化信创中产生新质生产力,医疗信创的部署如何实现，如何操作？

avatar DAMO开发者矩阵