# MySQL

# 安装

各个系统自行搜索教程

管理工具使用 Sequel Pro。
通过命令行连接数据库

mysql -h localhost -uroot -p123456

# 数据库与表

# 创建数据库

注意

不论是通过SQL还是管理界面创建数据库，务必保证编码格式为 utf8mb4，否则数据库表字段如果包含了Unicode字符会乱码。

CREATE DATABASE `sample_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE sample_db;

1
2
3

# 创建表

类型	使用场景
INT UNSIGNED	`id` 统一采用 `INT UNSIGNED` 类型，`INT UNSIGNED` 最大值约43亿。
TINYINT UNSIGNED	`TINYINT UNSIGNED` 最大值为255，用于存整型枚举。
TIMESTAMP	大部分情况下使用 `TIMESTAMP` 作为时间字段类型。虽然 `TIMESTAMP` 有 2038年问题，但是技术更新非常快，MySQL在十年之后是否会被淘汰仍然是个未知数，所以不必担心过多。
DATE	存储一个固定的日期比如生日
POINT	值格式: `POINT(<longitude>, <latitude>)`
BIT	BOOL型统一用这个。MySQL中关于BOOL字段类型表示十分混乱，这些估计都是早期遗留下来的问题。

注意

TINYINT(N) 和 INT(N) 实际上并不是限制这个字段存储最大值。这个需要跟 ZEROFILL 配合使用。比如 INT(4) ZEROFILL 类型，值为5显示的时候会填充3个0为 0005。

ZEROFILL相关文档

CREATE TABLE IF NOT EXISTS users
(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(250) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  avatar_url VARCHAR(250) DEFAULT '' NULL,
  sex TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别 0=not known | 1=male | 2=female | 9=not applicable',
  points INT DEFAULT 10000 NOT NULL,
  location POINT NULL COMMENT '用户地理位置',
  deleted_at TIMESTAMP NULL,
  is_admin BIT DEFAULT 0 NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uix_email (email)
) COMMENT '用户表';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 命名规范

所有的名字统一使用 snake_case 命名法。表名为复数。
使用全名，例如使用 article_id 而不是 aid，在不影响可读性的前提下，命名尽可能简短。
索引名称以 ix_{字段1}_{字段2} 的形式命名。普通索引加 ix_ 前缀，唯一索引加 uix_ 前缀。
bool类型字段统一加 is_ 或 has_ 前缀。如 is_enabled、is_activated。
时间戳使用 xxx_at 的形式命名，例如 created_at、updated_at。
操作人字段使用 xxx_by_id 的形式命名，例如 created_by_id、updated_by_id。
状态字段使用 status 或 xxx_status，不使用 state。
排序字段使用 display_order。

# 常用命令

DROP TABLE users; -- 删除表

ALTER TABLE users
  ADD COLUMN nickname VARCHAR(50) NOT NULL COMMENT '昵称' AFTER last_name; -- 添加字段

ALTER TABLE users
  MODIFY COLUMN last_name VARCHAR(75) NOT NULL; -- 修改字段

ALTER TABLE users DROP COLUMN nickname; -- 删除字段

ALTER TABLE products ADD KEY `ix_shop_id` (`shop_id`); -- 添加索引

ALTER TABLE members ADD UNIQUE KEY `uix_phone` (`phone`); -- 添加索引

ALTER TABLE products DROP KEY `ix_shop_id`; -- 删除索引

CREATE TABLE users_bak (SELECT * FROM users) -- 备份表数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 索引

对于数据量非常大的表，索引通常可以大幅度缩小查询范围，数据量不大的表没必要建索引。
联合索引创建需要知道 最左前缀匹配原则。比如有一张表 products(id, name, shop_id, category_id)，如果查询语句是 SELECT * FROM products WHERE shop_id=? AND category_id=?，只需要添加 ix_shop_id_category_id 索引，这时候MySQL会按照 最左前缀匹配原则 通过 shop_id 过滤掉大部分数据，然后通过 category_id 再过滤一部分数据。
查询语句里面的查询条件先后顺序不影响查询性能。

注意

即使是查询 SELECT * FROM products WHERE shop_id=? 也不需要再单独创建 shop_id 索引了，因为 ix_shop_id_category_id 已经包含了 shop_id 索引。
SELECT * FROM products WHERE category_id=? 因为语句没有 shop_id，所以按照 最左前缀匹配原则 是用不到任何索引的。
通过索引查询的数据需要加 ORDER BY，否则 SELECT 出来的数据极有可能不是默认按主键排序的。

ALTER TABLE products ADD KEY `ix_shop_id_category_id` (`shop_id`, `category_id`); -- 添加联合索引

EXPLAIN 可以清晰的评估加索引之后查询影响的行数。

EXPLAIN SELECT * FROM products WHERE shop_id=? AND category_id=?;

# 地理位置

存储位置信息统一使用 POINT 字段类型。

-- 更新用户地理位置
UPDATE `users` SET `location`=POINT(119.2604007, 38.0396771) where id=?

1
2

常见的场景是根据当前用户地理位置找出附近的店铺，按最近距离排序。ST_Distance_Sphere 用于计算两个位置之间的距离，单位为米。

SELECT `id`, `name`, ST_Distance_Sphere(`location`, ST_GeomFromText('POINT(119.2604007, 38.0396771)')) AS `distance`
FROM `stores` 
ORDER BY `distance` DESC
LIMIT 100;

1
2
3
4

文档

Choose mode

Mysql