mysql日常使用经验积累

注意事项：

索引

• KEY与INDEX的区别
  索引（INDEX）负责维护表的查找速度和操作速度，约束（KEY）负责维护表单完整性。同时，在mysql中的索引是约束索引（即创建索引自动也会创建约束），并且创建约束也会自动附带索引。之所以会这样做，因为mysql中的约束效果是通过索引实现的，例如mysql判断当前列是否是unique就是通过unique索引来判断的。
  在理论上是不能将KEY与INDEX划等号的，但在实际使用中，他们基本上么什么区别。

• 索引查找sql优化
  1、如果索引多个字段，第一个字段最好用经常作为查询条件的。
  2、长字段支持前缀索引
  3、索引必须有足够的区分度
  4、如果一个表数据很多，可以拆分成多个表

• 唯一约束
  唯一约束是指所有记录中的字段不能出现重复值。唯一约束与主键约束相似，都能确保列的唯一性。不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列允许有空值，但是只能有一个。而主键约束一个表中只能有一个，且不允许有空值。
  联合唯一索引：
  UNIQUE KEY uniq_user_id_dept_id、(user_id, dept_id)这样user_id和dept_id的联合字段就不能重复了
  例如：

CREATE TABLE `user_dept` (
  `id`            BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
  `user_id`         BIGINT UNSIGNED NOT NULL COMMENT '用户id',
  `dept_id`        BIGINT UNSIGNED NOT NULL COMMENT '部门id',
  `create_time` BIGINT UNSIGNED NOT NULL COMMENT ’创建时间',
  UNIQUE KEY `uniq_user_id_dept——id`(`user_id`, `dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色部门关系表';

字段

• 字段选择
  1、字段选择的一般原则是保小不保大，能用占用字节小的字段就不用大字段。对于主键强烈建议用自增类型，这样能节省空间。对于时间存储有datetime和timestamp，datetime占用8个字节，而timestamp占用4个字节，所以没有特殊必要一定要有timestamp。

• 字段名不要使用desc，desc是mysql的关键字。
  1、desc tablename用来获取表的数据结构；
  2、desc在order by中代表倒叙，例如： select * from table order by id desc（asc）

• 对于设置为非null的varchar字段，为了防止传入null，加一个StringUtils.trimToEmpty(str)，最好处理一下容错。

类型

• 数值类型：

类型	大小（字节）	范围（有符号）	范围（无符号）
bigint	8	-2^63 ~ 2 ^63 - 1	0 ~ 2^64 - 1
int	4	-2^31 ~ 2 ^31 - 1	0 ~ 2^32 - 1
smallint	2	-2^15 ~ 2 ^15 - 1	0 ~ 2^16 - 1
tinyint	1	-2^7 ~ 2 ^7 - 1	0 ~ 255

• 字符串类型：

类型	范围（字节）
varchar	最大长度：65535 (2^16 - 1)
tinytext	255 (2 ^8 - 1)
text	65535 (2^16 - 1)
mediumtext	2^24 - 1
longtext	2^32 - 1
tinyblob	255 (2 ^8 - 1)
blob	65535 (2^16 - 1)
mediumblob	2^24 - 1
longblob	2^32 - 1

text：用来保存字符数据；blob：用来保存二进制数据。它们两个超过长度都会截断，并产生一条sql警告。
**注意：**如果给text、blob加索引，必须制定前缀索引；text、blob不能有默认值。

字符集

• gbk
  gbk编码存储的最大字符长度2字节。
• utf8
  utf8编码存储的最大字符长度为3字节，如果遇到4个字节的字符就会插入异常。三个字节的utf8最大能编码的Unicode字符是0xffff，也就是Unicode中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的Unicode字符，都无法使用mysql的utf8存储。包括Emoji表情等
• utf8mb4
  utf8mb4最多存储4个字节。

排序字符集：COLLATION

字符除了需要存储，还需要排序或比较大小。
utf8mb4对应的排序字符集有utf8mb4_unicode_ci、utf8mb4_general_ci.

• utf8mb4_unicode_ci：基于标准的Unicode来排序和比较，能够在各种语言之间精确排序。其实，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法，所以性能比utf8mb4_general_ci慢一些。
• utf8mb4_general_ci：没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。在比较和排序的时候更快。

其实，在绝大多数情况下，不会发生复杂比较。相比选择哪一种collation，使用者更应该关心字符集与排序规则在db里需要统一。

SQL语句

• 插入语句
  批量插入：key重了会更新时间

CREATE TABLE `user_dept` (
  `id`            BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
  `user_id`         BIGINT UNSIGNED NOT NULL COMMENT '用户id',
  `dept_id`        BIGINT UNSIGNED NOT NULL COMMENT '部门id',
  `create_time` BIGINT UNSIGNED NOT NULL COMMENT ’创建时间',
  UNIQUE KEY `uniq_user_id_dept——id`(`user_id`, `dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色部门关系表';

insert into `user_dept` (user_id,dept_id, create_time) values (1, 1, 1), (2, 2, 2) on duplicate key update create_time = now;