MySQL 数据类型

VARCHAR

VARCHAR(max_length)  //5.0版本以上，varchar(20)，指的是20字符

VARCHAR 允许的最大长度为 65535 个字节，这也是 MySQL 中的行大小的限制

因此如果使用UTF8的最大长度为(65535-1-2)/3

减1是因为实际存储从第2个字节开始减2则因为要在列表长度存储实际字符长度除3是因为utf8编码限制

例：

create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
N的最大值：(65535-1-2-4-30*3)/3

VARCHAR 截断内容

当插入的内容超过 VARCHAR 列定义的长度时，MySQL 会采用如下策略：

如果超过的部分只包含空格，则多余的空格会被截断。
如果超过的部分不只是空格，则给出错误提示。

CHAR

CHAR(length) //默认1 最大255  性能要比 VARCHAR 更好

写入 CHAR 列中的字符串的长度小于指定的字符长度，MySQL 会在源字符串后填充空格一直到指定的长度。当您读取 CHAR 列中的值时，MySQL 会删除后面的空格

后缀空格问题：即使原来存的是'Tom ' 取出也会变成 'Tom'

插入超过长度的值会直接返回错误

INT

类型	字节数
`TINYINT`	`1`
`SMALLINT`	`2`
`MEDIUMINT`	`3`
`INT`	`4`
`BIGINT`	`8`

INT 和 INTEGER 是同义词。

自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；
MySQL 8.0 版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；

在删除自增为 3 的这条记录后，下一个自增值依然为 4（AUTO_INCREMENT=4），这里并没有错误，自增并不会进行回溯。但若这时数据库发生重启，那数据库启动后，表 t 的自增起始值将再次变为 3，即自增值发生回溯

当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
账户余额字段，设计是用整型类型（BIGINT），而不是 DECIMAL 类型，这样性能更好，存储更紧凑

DATE

MySQL DATE 使用 yyyy-mm-dd 格式来存储日期值。如果您想以其他的日期格式显示，比如 mm-dd-yyyy，您可以使用 DATE_FORMAT 函数将日期格式化为您需要的格式。

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today      |
+------------+
| 03/25/2022 |
+------------+

DATETIME 和 TIMESTAMP

TIMESTAMP 需要 4 个字节，而 DATETIME 需要 8 个字节。TIMESTAMP 和 DATETIME 二者都需要额外字节存储小数秒。

TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 。如果要存储超过 2038 年的时间值，则应使用 DATETIME 代替 TIMESTAMP 。

MySQL TIMESTAMP 以 UTC 值存储(会从当前时区转换到UTC)。但是，MySQL 将 DATETIME 值按原样存储，没有时区。

YEAR

YEAR数据类型占用 1 个字节，YEAR值的范围为从1901到2155, 还有0000

定义为 YEAR 数据类型的列可以接受多种输入格式，包括：

4 位数字的年份值，从 1901 to 2155。
4 位数字的年份值的字符串形式，从 '1901' 到 '2155'。
2 位数字的年份值，从 0 到 99，并按如下规则转换为 4 位数年份：
- 1 到 69 转换为 2001 到 2069。
- 70 到 99 转换为 1970 到 1999。
- 0 转换为 0000。
2 位数字的年份值的字符串形式，从 '0' 到 '99'，并按如下规则转换为 4 位数年份：
- '0' 到 '69' 转换为 2000 到 2069。
- '70' 到 '99' 转换为 1970 到 1999。

问题自测

char 和 varchar 的区别是什么？

char：固定长度，分配固定的存储空间，存储的数据长度小于指定的长度，剩余空间会用空格来填充（取出时会删除后面空格，所以原来字符串有空格也会被删）。char类型在存储固定长度的数据时比较高效。
varchar：可变长度的字符数据类型。varchar类型存储的数据长度是可变的，数据库会根据实际存储的数据长度来分配存储空间。相比于char类型，varchar类型在存储可变长度的数据时比较节省空间。（不会进行空格填充。比如如果定义一个varchar(10)类型的字段，并存储了一个长度为5的字符串，那么它只会占用5个字节的存储空间，并且还会额外用1-2字节存储「可变长字符串长度」的空间。
理论上CHAR比VARCHAR快的根本原因是站在CPU的角度来说的（物理储存连续），但性能是综合各种因素后的最终结果，当Innodb buffer pool小于表大小时，磁盘读写成为了性能的关键因素，而VARCHAR更短，因此性能反而比CHAR高。但是当Innodb buffer pool足够大时，CHAR和VARCHAR性能没有太大的差别。

varchar(100)和 varchar(10)的区别是什么？

存储的最大长度为多少个字符，实际占用的存储空间取决于存储的数据长度

假如说一个字段是varchar(10)，但它其实只有6个字节，那他在内存中占的存储空间是多少？在文件中占的存储空间是多少？

保存到文件的时候，只会存储实际使用的字符串大小。但是内存是会按varchar最大值来固定分配大小

因此：内存会占用10字节，文件存储空间会占用6字节，并且还会额外用1-2字节存储「可变长字符串长度」的空间。

decimal 和 float/double 的区别是什么？存储⾦钱应该⽤哪⼀种？

decimal 类型的值以十进制方式存储，这意味着它们可以精确地表示小数，不会出现浮点数计算中的舍入误差。
float 和 double 类型的值以二进制方式存储，它们在存储和计算时可能会引入舍入误差，特别是在执行多次计算后会逐渐积累误差。
float使用4个字节存储，double使用8个字节，
decimal类型的数据存储形式是，将每9位十进制数存储为4个字节，decimal(M,D)，其中， M 的范围是1~65， D 的范围是0~30，而且D不能大于M。字段decimal(5,2)，5-2=3，其中小数部分为2，整数部分为3

为什么不推荐使⽤ text 和 blob？

BLOB（Binary Large Object）用于存储二进制数据，而TEXT用于存储字符数据

1.不适合建索引

2.数据处理和转换的复杂性增加

3.降低数据的可读性和维护性

count主键和count非主键结果会不同吗

count()函数是返回表中某个列的非NULL值的数量。

由于主键的列不能存NULL值，所以count(主键)返回的结果，可以表示数据库表中所有行数据的数量。
由于非主键的列可以存NULL值，那么count(非主键)返回表中非主键列的非NULL值的数量。

count(1)和 count(*)区别，哪个性能好

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描，没有就读聚簇索引。

count(1)表示不为NULL，等价于count(0)也就是 count(*)，相比于count(主键字段) 不会读取记录中的任何字段的值，因此会快一点

使用 count(字段) 来统计记录个数，它的效率是最差的，会采用全表扫描的方式来统计。如果非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

MySQL内连接、外连接有什么区别

内连接和外连接都是用于连表查询。内连接是只返回两个表匹配的数据行，外连接可以返回两个表匹配和不匹配的数据行，外连接主要分为左连接和右连接。

左连接返回左表中的所有行和右表中匹配的行。如果右表中没有匹配的行，则用NULL值填充
右连接返回右表中的所有行和左表中匹配的行，如果左表中没有匹配的行，则用NULL值填充

外连接时on和where过滤条件区别

内连接on与where等价

在外连接中，使用on和where过滤条件的区别在于：

on用于指定连接两个表的条件，通常用于指定两个表之间的关联条件，即连接条件，在连接时进行过滤。
where用于指定过滤条件，对连接后的结果集进行进一步筛选。

having与where的区别

在GROUP BY分组查询过程中，

Where是工作在GROUP BY之前，Where是对分组之前的数据进行筛选，无法使用聚合函数，

Having是工作在GROUP BY之后，Having主要对分组之后的数据进行筛选，可以使用聚合函数。

delete、drop、truncate有什么区别

delete是删除表中的数据，我们可以选择删除部分数据或者全部数据，delete删除的数据是可以回滚的，delete操作并不是真的把数据删除掉了，而是给数据打上删除标记，目的是为了空间复用，所以delete删除表数据，磁盘文件的大小是不会缩减的。

drop是删除表结构和表中所有的数据，truncate是只删除表中所有的记录，表结构并不会被删除，drop和truncate删除的数据都是不可以回滚的，并且删除表会立刻释放磁盘空间。

从删除表的性能来看，drop>truncate>delete。

联合查询中union和union all的区别是什么

UNION：在合并结果集后会自动剔除重复的行。
UNION ALL：则会保留所有的重复行，不会进行去重操作

数据库三大范式是什么

第一范式（1NF）

原子性：保证数据不可再分

tags
java,python        //一列一个数据

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情，就是主键对应着所有信息

学生表
学号id 姓名    课程  //课程不应该属于学生表

第三范式（3NF）

前提：满足第一和第二范式

第三范式需要保证表中的数据和主键直接相关，而不是间接相关

存款    花费    余额
100      10    90

一范式要求所有属性都是不可分的基本数据项；
二范式目的是解决部分依赖；
三范式目的是解决传递依赖。

在实际的工程实践上没有必要严格遵循三范式要求，比如说可以通过字段冗余的设计，避免联表查询。

MySQL （一）数据类型与SQL语法