DataGrip 2022 更新了建表 UI，比之前功能更多，也更好用了。在此结合它来复习一下建表相关的知识。

## 表

- Engine: 优先选择 InnoDB，它是事务型的，支持行级锁。
- Collection: 创建数据库时使用 utf8mb4_general_ci ；创建表时不填写即可，会根据数据库的设置来优先选择。

## 列

Date Type:

- 字符串: char 是定长的，如手机号码；varchar  可变长字符串，如用户名；
- 大文本: text 大文本，文章详情、json串等使用，非必要时不使用；索引需要使用范围索引；
- 日期、时间: datetime
- 整数: tinyint， 0 ~ 255 之间，用于存年龄、状态值等； int，有 42 亿的范围，适合大部分场景；bigint，几百亿；INT(11) 中的数字只是规定了显示字符的个数，不影响存储和计算。
- 浮点数：double

Not Null

- 大部分情况下不推荐使用可空列，勾上该 Not Null 选项
- 字符串默认值 `''`, 数值默认值 `0`
- 软删除如果需要使用 delete_time 字段，则使用可空列

Auto Increment

- int 类型主键自增时使用

Default Expression

- 默认值，如日期类型指定 CURRENT_TIMESTAMP

On Update

- 更新时设置，如 update_time 字段指定 CURRENT_TIMESTAMP

## 键

- 需建立主键、唯一键等，可在“列”中通过右键创建
- 可选中多个字段后按右键创建键，如创建多个字段结合的唯一键

## 索引

- 不使用外键索引，通过 ORM 代码层面建立关联关系
- 索引选择性高时，才需要加索引
- text 类型需使用范围索引
- 联合索引按最左前缀顺序进行匹配才会命中
- 如果已存在联合索引，对于前缀部分不需要再创建索引



结合 DataGrip 新版建表 UI 复习 MySQL 建表知识

## 问题

通过 DataGrip 给一个 not null 的 datetime 类型字段设置默认值为 CURRENT_TIMESTAMP 时，产生语句为：

```js
alter table diary
    alter column publish_time set default CURRENT_TIMESTAMP;
```

执行该语句，产生了一个报错信息：

```
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP' at line 2.
```

## 解决方案

设置 CURRENT_TIMESTAMP 为默认值时需要通过 modify 语句来进行，更改为以下语句：

```
alter table collection
    MODIFY column create_time datetime NOT NULL default CURRENT_TIMESTAMP;
```

DataGrip 中修改：可额外修改该列的一些内容，比如改一下列备注信息，语句会变为 modify，执行即可成功修改。

解决 DataGrip 生成的 set default CURRENT_TIMESTAMP 语句报错问题


使用数据库工具，创建唯一索引：

```sql
create unique index diary_code_uindex on diary (code);
```

创建索引时遇到报错：

```
Duplicate entry '' for key 'diary_code_uindex'
```

这是因为该字段的数据中，存在了空字符串，所以创建的时候报了这个错误。

唯一索引允许存在多个 null, 但对于空字符串是进行检测的，不允许存在重复的“空字符串”，所以出错了。

把该字段的默认值改为 null, 再把空字符串改为 null，即可成功创建。


MySQL 创建唯一索引时，解决 Duplicate entry for key 问题


create_time 和 update_time 字段，可以通过代码逻辑来添加，比如通过 orm 框架自动写入。

该功能通过 mysql 本身就可以实现，就免去不同语言、不同框架中特定的代码处理逻辑。

```
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
```

把原有未设置该机制的  create_time 和 update_time 字段升级：

```
ALTER TABLE diary
  MODIFY COLUMN create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE diary
  MODIFY COLUMN update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
```

MySQL 使用笔记及查漏补缺

结合 DataGrip 新版建表 UI 复习 MySQL 建表知识

解决 DataGrip 生成的 set default CURRENT_TIMESTAMP 语句报错问题

MySQL 创建唯一索引时，解决 Duplicate entry for key 问题

mysql 设置 DEFAULT 和 ON UPDATE 自动更新 create_time 和 update_time