Jade Dungeon

MySQL例子

MySQL例子

MySQL例子

配置MySQL 字符集

修改字符集，配置在my.ini：

--在 [mysqld] 标签下加上以下内容：
default-character-set = utf8
character_set_server = utf8
-- 注意：如果此标签下已经存在“default-character-set=GBK”类似的内容，只需修改即可。

--在 [mysql]  标签下加上一行
default-character-set = utf8
--在 [mysql.server]标签下加上一行
default-character-set = utf8
--在 [mysqld_safe]标签下加上一行
default-character-set = utf8
--在 [client]标签下加上一行
default-character-set = utf8

重启mysql服务，然后检查：

mysql> show variables like 'char%'; show variables like 'collation%';
+--------------------------+----------------------------+                                                                                                                       
| Variable_name            | Value                      |                                                                                                                       
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql>

删除重复的数据

/* 删除重复的数据，只保留id最小的 */
SELECT `t1`.*
FROM `t1`,(
  SELECT DISTINCT MIN(`id`) AS `id`,`name`,`add`
  FROM `t1`
  GROUP BY `name`,`add`
  HAVING COUNT(1) > 1
) AS `t2`
WHERE `t1`.`name` = `t2`.`name`
  AND `t1`.`add` = `t2`.`add`
  AND `t1`.`id` <> `t2`.`id`;
+----+------+-----+
| id | name | add |
+----+------+-----+
|  2 | abc  | 123 |
|  4 | abc  | 123 |
|  7 | xzy  | 456 |
|  8 | xzy  | 456 |
| 11 | xzy  | 789 |
| 13 | ijk  | 147 |
| 20 | tpk  | 963 |
| 22 | wer  | 546 |
+----+------+-----+

修改数据库字符集

修改数据库字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]

如：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

如：

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; 

ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE continent continent VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE country country VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE countryurl countryurl VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE city city VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE city city VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE city city VARCHAR(50) CHARACTER SET = utf8mb4 ;
ALTER TABLE `htlvendorpricemdb`.`agoda_citylist_intl` CHANGE city city VARCHAR(50) CHARACTER SET = utf8mb4 ;

# 查看数据库编码：
SHOW CREATE DATABASE db_name;  

# 查看表编码：
SHOW CREATE TABLE tbl_name;  

# 查看字段编码：
SHOW FULL COLUMNS FROM tbl_name;

日期时间

mysql中创建时间和更新时间的区别原创

`create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',

基本设计原则

自增UUID

UUID 也可以当主键，自增 UUID 比自增主键性能更好，多占用的空间也可忽略不计

MySQL8check函数约束

对于固定选项值的字段，MySQL8 以前推荐使用枚举字段，MySQL8 以后使用check函数约束，不要使用 0，1，2 表示

tinyint的缺点：

表达不清：这个表可能是其他同事设计的，你印象不是特别深的话，每次都需要去看字段注释，甚至有时候在编码的时候需要去数据库确认字段含义
脏数据：虽然在应用层可以通过代码限制插入的数值，但是还是可以通过sql和可视化工具修改值

MySQL 8以前推荐使用ENUM枚举字符串类型，外加SQL_MODE的严格模式。在MySQL 8.0.16 以后的版本，可以直接使用check约束机制，不需要使用enum枚举字段类型

表的索引个数可以超过5

一张表的索引个数并没有限制不能超过5个，可以根据业务情况添加和删除

MySQL8对子查询优化

MySQL8 对子查询有了优化，可以放心使用。

老版本的 MySQL 数据库对子查询优化有限，所以很多 OLTP 业务场合下，我们都要求在线业务尽可能不用子查询。

然而，MySQL 8.0 版本中，子查询的优化得到大幅提升，所以在新版本的MySQL中可以放心的使用子查询。

子查询相比 JOIN 更易于人类理解，比如我们现在想查看2020年没有发过文章的同学的数量

SELECT COUNT(*)
FROM user
WHERE id not in (
    SELECT user_id
    from blog
    where publish_time >= "2020-01-01" AND  publish_time <= "2020-12-31"
)

可以看到，子查询的逻辑非常清晰：通过 not IN 查询文章表的用户有哪些。

如果用left join写

SELECT count(*)
FROM user LEFT JOIN blog
ON user.id = blog.user_id and blog.publish_time >= "2020-01-01" and blog.publish_time <= "2020-12-31"
where blog.user_id is NULL;

可以发现，虽然 LEFT JOIN 也能完成上述需求，但不容易理解。

我们使用 explain查看两条 sql 的执行计划，发现都是一样的，不论是子查询还是 LEFT JOIN，最终都被转换成了left hash Join，所以上述两条 SQL 的执行时间是一样的。即，在 MySQL 8.0 中，优化器会自动地将 IN 子查询优化，优化为最佳的 JOIN 执行计划，这样一来，会显著的提升性能。