path: root/MySQL使用过程问题小结-1.md

## MySQL 使用过程问题小结
### 数据库设计&使用规范
#### 数据库&表&字段命名规范
[参考摘抄](https://www.cnblogs.com/slgkaifa/p/7245519.html)

1. 表名一般以 __模块名称_详细表名__ 来实现，同一个模块的前缀是一样的
2. 表名称不应该取得太长（ __一般不超过三个英文单词__ ，不推荐使用中文拼音，总长度不要超过30个字符）
3. 一些作为 __多对多连接表__ ，能够 __使用两个表的前缀作为表名__ ：如：用户登录表User_Login。用户分组表User_GroupInfo，这两个表建立多对多关系的表名为：User_Group_Relation（关系统一用Relation）
4. 注意一点， __主键在做其它表的外键时__ ，或者在被其它表引用时， __字段说明和字段名尽量保持一致__ 。比方发帖表BBS_Topic里的用户字段写成UI_ID，这样跟用户信息表User_Info的主键UI_ID保持一致，看起来舒服，相应关系非常明白，也不容易错，前后不一致时easy令人费解。
5. 当系统中有一些少量的、反复出现的值时， __使用字典表来节约存储空间和优化查询__ 。如地区、系统中用户类型的代号等。这类值不会在程序的执行期变化，可是须要存储在数据库中。 __一般数据库中都有一个数据字典表__ ，用来保存系统所用到的基础数据，大型的字段表如省份城市区域的字典表， __统一以Dictionary_作为前缀__ 。建一个基础数据字典表的作用非常重要，其它的比方地区表(Sys_DicArea)，汉语拼音表（Sys_DicCharacter）(用来汉字和拼音的转换)由于数据量较大，单独建表。
6. 与字段有关， __默认的一些特殊字段__ 。比方一些业务处理表中，除了加入生成的自己主动编号ID（一般作为主键用），该记录创建的时间CreateDate(创建时间)、该记录的创建人CreatBy（注意这里没UI_ID（用户信息表User_Info的主键UI_ID）,由于还有改动人），最后改动人LastEditBy。最后改动时间LastEditDate。（这些能够直接使用中文字符，而不使用编码，提高查询的效率）同一时候有的时候须要注意， __删除的时候并不真的删除该记录__ ，而是 __加入一个标识位__ ，比方XX_DeleteStaus删除状态。1是有效的，0则是无效的。
7. __命名表时，用单数形式表示名称__ 。比如。使用 Employee，而不是 Employees。
8. 数据库中应建立这样一个表，就是 __数据库本身的字段信息__ 。表的说明，也就是数据库设计文档的一个表。方便查询使用，有什么不明的能够直接从数据库查询，数据库文档丢失，凝视丢失，都能够又一次起作用。
9. __每一个表都应该有一个主键，这个主键最好是数字，并且是递增的__ 。有非常多表的主键用32位字符编码，这样做的目的很多其它的是从安全考虑的。由于字符多时索引时效率低，而使用自增列也不是非常少，比方加入主表和从表操作时。主表的主键是从表的外键，这个时候还有取返回值，然后再加入，不能够同一时候加入。主键能够用自己定义的规则，大部分用MAX（ID）的做法，也能够自己定义一个序列表，有点像序列。或者用时间的年月日秒详细到毫秒。关于列的命名,建议对数据类型也做一些规范，由于非常easy确定。仅仅有四种主要类型：数字，字符，时间，逻辑值，这些在类型上和长度上都能够定好规范，统一起来。
10. __操作日志表，登录日志表，这是数据库中必备的两个表__ 。这个记录也须要做进一步的保存。这个有两种情形，一是详细到单个字段的操作日志。二是整个表的操作日志。
常见的几个表详细说明：操作日志表Sys_OperateLog、登录日志表Sys_LoginLog、系统字典表Sys_Dictionary、系统字典表类型Sys_DicType

[参考摘抄](https://www.cnblogs.com/pangguoming/p/7126512.html)

1. 数据库命名规范：采用 __26个英文字母__ (区分大小写)和0-9的自然数(经常不需要)加上 __下划线\___ 组成，命名简洁明确，多个单词用下划线_分隔，一个项目一个数据库，多个项目慎用同一个数据库
2. 数据表命名规范：__全部小写命名，禁止出现大写__； __禁止使用数据库关键字__ ，如：name，time ，datetime，password等；表的名称一般 __使用名词或者动宾短语__ ；明细表的名称为：主表的名称+字符dtl（detail缩写）；表必须 __填写描述信息__ （使用SQL语句建表时）。命名规范：①模块_+功能点  示例：alllive_log   alllive_category；②功能点  示例：live   message；③通用表  示例：all_user。统一命名规则， __动宾短语分离且动宾逻辑顺序统一__ 。
3. 数据库字段命名规范：在命名表的列时， __不要重复表的名称__ ；不要在列的名称中包含数据类型；字段命名 __使用完整名称__ ，禁止缩写
4. 所有字段在设计时，除以下数据类型timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、binary 、varbinary外， __必须有默认值__ ， __字符型的默认值为一个空字符值串''，数值型的默认值为数值0，逻辑型的默认值为数值0__ ，系统中所有逻辑型中数值0表示为“假”，数值1表示为“真”，datetime、smalldatetime类型的字段没有默认值，必须为NULL
5. 用尽量少的存储空间来存储一个字段的数据：使用int就不要使用varchar、char；用varchar(16)就不要使varchar(256)；IP地址使用int类型；固定长度的类型最好使用char，例如：邮编(postcode)；能使用tinyint就不要使用smallint，int；最好给每个字段一个默认值，最好不能为null；字符转化为数字(能转化的最好转化，同样节约空间、提高查询性能)；避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效)；少用text类型(尽量使用varchar代替text字段)
6. 数据库中每个字段的规范描述：(1)尽量遵守第三范式的标准(3NF)，表内的每一个值只能被表达一次，表内的每一行都应当被唯一的标识，表内不应该存储依赖于其他键的非键信息；(2)如果字段事实上是与其它表的关键字相关联而未设计为外键引用，需建索引；(3)如果字段与其它表的字段相关联，需建索引；(4)如果字段需做模糊查询之外的条件查询，需建索引；(5)除了主关键字允许建立簇索引外，其它字段所建索引必须为非簇索引；

#### SQL语言编码规范
1. 所有关键字必须大写，如：INSERT、UPDATE、DELETE、SELECT及其子句，IF……ELSE、CASE、DECLARE等
2. 所有函数及其参数中除用户变量以外的部分必须大写
3. 在定义变量时用到的数据类型必须小写

#### 注释编写规范
注释可以包含在批处理中，在触发器、存储过程中包含描述性注释将大大增加文本的可读性和可维护性，本规范建议： 

1. 注释以英文为主，实际应用中，发现以中文注释的SQL语句版本在英文环境中不可用，为避免后续版本执行过程中发生某些异常错误，建议使用英文注释
2. 注释尽可能详细、全面创建每一数据对象前，应具体描述该对象的功能和用途，传入参数的含义应该有所说明，如果取值范围确定，也应该一并说明，取值有特定含义的变量（如boolean类型变量），应给出每个值的含义
3. 注释语法：单行注释、多行注释；单行注释：注释前有两个连字符（--）对变量、条件子句可以采用该类注释；多行注释：符号之间的内容为注释内容，对某项完整的操作建议使用该类注释
4. 注释简洁，同时应描述清晰
5. 函数注释： 编写函数文本--如触发器、存储过程以及其他数据对象--时，必须为每个函数增加适当注释，该注释以多行注释为主

### 数据库安装&登录&退出
1. 数据库安装包下载及安装：

        ```bash
        rpm -i mysql-community-release-el7-5.noarch.rpm
        yum install mysql-community-server
        ```

2. mysql 服务启动停止查看：

        ```bash
        systemctl start mysql # 启动 mysql 
        systemctl stop mysql # 停止 mysql
        systemctl status mysql # 查看 mysql 状态
        ```

3. 安装后初始配置（建立数据库登录用户及密码）：`mysqladmin -u root password`

        ```bash
        mysqladmin -u root password # 新建用户名及密码
        chkconfig --level 2345 mysqld on # 设置 mysql 服务的运行级信息
        systemctl start mysql # 重启 mysql 服务

        [root@iz2ze8e1dj06n0wjqj19n2z ~]# systemctl start mysql
        [root@iz2ze8e1dj06n0wjqj19n2z ~]# mysqladmin -u root password
        New password: 【此处输入密码】
        Confirm new password: 【此处重复输入密码确认】

        [root@iz2ze8e1dj06n0wjqj19n2z ~]# chkconfig --level 2345 mysqld on
        Note: Forwarding request to 'systemctl enable mysqld.service'.
        ```

4. 初始配置后正常运行/登录mysql：`mysql -u root -p`

        ```bash
        mysql -u root -p
        【此处输入密码】
        [root@iz2ze8e1dj06n0wjqj19n2z ~]# mysql -u root -p
        Enter password: 
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.6.43 MySQL Community Server (GPL)

        Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.

        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

        mysql> 
        ```

5. 退出 mysql 命令行界面：`exit` 回车和 `ctrl+d`

        ```bash
        mysql> exit
        Bye
        mysql> ^DBye
        ```

### 数据库表操作&管理
1. 查看数据库列表：`show databases;`

        ```sql
        mysql> show databases;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        +--------------------+
        3 rows in set (0.00 sec)

        mysql> 
        ```

2. 创建数据库：`CREATE DATABASE [数据库名];`

        ```sql
        mysql> CREATE DATABASE dns;
        Query OK, 1 row affected (0.00 sec)
        ```

3. 使用数据库：`use [数据库名];`

        ```sql
        mysql> use dns;
        Database changed
        ```

4. 查看数据库的表列表：`show tables;`

        ```sql
        mysql> show tables;
        Empty set (0.01 sec)
        ```

5. 创建用户：`CREATE USER '[用户名]'@'localhost' IDENTIFIED BY '[用户密码]';`

        ```sql
        mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '111111';
        Query OK, 0 rows affected (0.01 sec)

        [root@iz2ze8e1dj06n0wjqj19n2z ~]# mysql -u root -p
        Enter password: 111111
        ```

6. 给用户赋权：`GRANT CREATE,DROP,ALTER,INSERT,UPDATE,SELECT,DELETE,INDEX ON [数据库名].* TO '[用户名]'@'localhost';`

        ```sql
        mysql> GRANT CREATE,DROP,ALTER,INSERT,UPDATE,SELECT,DELETE,INDEX ON dns.* TO 'root'@'localhost';
        Query OK, 0 rows affected (0.01 sec)
        mysql> FLUSH PRIVILEGES; # 刷新权限
        Query OK, 0 rows affected (0.01 sec)
        ```

7. 创建数据库表：`CREATE TABLE [表名]([字段1], [字段2], [字段3]……);`

        ```sql
        CREATE TABLE employee (
        employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        last_name VARCHAR(30) NOT NULL,
        first_name VARCHAR(30) NOT NULL,
        email VARCHAR(100) NOT NULL,
        hire_date DATE NOT NULL,
        notes MEDIUMTEXT,
        PRIMARY KEY (employee_id),
        INDEX (last_name),
        UNIQUE (email)
        )
        ENGINE=InnoDB
        DEFAULT CHARSET = utf8; # 修改数据库表默认编码方式
        # 遇到问题：修改编码格式后，UNIQUE的那个字段长度太长，超过索引支持
        ```

8. 修改数据库&表默认编码：[参考](https://www.cnblogs.com/shootercheng/p/5836657.html )

        ```sql
        mysql> show variables like 'character_set_database';
        +------------------------+--------+
        | Variable_name          | Value  |
        +------------------------+--------+
        | character_set_database | latin1 |
        +------------------------+--------+
        1 row in set (0.00 sec)

        create database <数据库名> character set utf8; # 创建数据库时添加字符编码设置
        create table tb_books (
            name varchar(45) not null,
            price double not null,
            bookCount int not null,
            author varchar(45) not null ) default charset = utf8; # 创建表时添加字符编码设置

        alter database <数据库名> character set utf8; # 修改数据库字符编码
        alter table <表名> character set utf8; # 修改表字符编码
        alter table <表名> change <字段名> <字段名> <类型> character set utf8; # 修改字段字符编码【这里就是要重写两遍字段名】
        alter table user change username username varchar(20) character set utf8 not null;
        # PS：修改编码格式时，若仅修改数据库编码，对于已经建好的表，编码格式不会改变，只对新建的表有用；所以对于已经建好的表，一定要修改到字段才可以，仅修改数据库和表还不行
        ```

9. 添加&修改&删除唯一约束：[MySQL 唯一约束 (UNIQUE KEY)](http://c.biancheng.net/view/2445.html)；[MySQL 创建表多个字段的唯一约束](https://blog.csdn.net/john1337/article/details/84141087)

        ```sql
        # 1. 在创建表时设置唯一约束：在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法规则：
        # <字段名> <数据类型> UNIQUE
        mysql> CREATE TABLE tb_dept2
            -> (
            -> id INT(11) PRIMARY KEY,
            -> name VARCHAR(22) UNIQUE, # 唯一约束
            -> location VARCHAR(50)
            -> );
        # 2. 在修改表时添加唯一约束：在修改表时添加唯一约束的语法格式为：
        # ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
        mysql> ALTER TABLE tb_dept1
            -> ADD CONSTRAINT unique_name UNIQUE(name);
        # 3. 删除唯一约束：在 MySQL 中删除唯一约束的语法格式如下：
        # ALTER TABLE <表名> DROP INDEX <唯一约束名>;
        mysql> ALTER TABLE tb_dept1
            -> DROP INDEX unique_name;
        # 4. 多字段组合唯一约束，mysql 提供了两种方式：
        alter table xx add unique key no_account(no,collection_account) # unique key
        alter table xx add unique no_account_index(no,collection_account); # unique index
        ```

10. 唯一约束长度太长导致建表出错：`修改 mysql 全局配置并删表重建添加表动态属性`；参考[MySQL唯一约束长度限制](https://blog.51cto.com/13120271/2315189)；参考[修改Mysql索引长度限制](https://blog.csdn.net/u010719917/article/details/70598813)：对row_format为dynamic格式 ,可以指定索引列长度大于767 bytes。但是索引列总长度的不能大于3072 bytes的限制仍然存在；参考[mysql改变innodb_large_prefix](http://www.voidcn.com/article/p-uoferwcl-bwa.html) 默认约束长度为 767 字节，来源是因为之前 utf8 256*3字节-1 = 767 字节。可以通过修改 innodb_large_prefix 参数扩展至 3072 字节，文章说：一般情况下不建议使用这么长的索引，对性能有一定影响；最后这篇文章提出来的最终版原因是：MySQL 索引只支持767个字节，utf8mb4 每个字符占用4个字节，所以索引最大长度只能为191个字符，即varchar(191)，若想要使用更大的字段，mysql需要设置成支持数据压缩，并且修改表属性 row_format ={DYNAMIC|COMPRESSED}。最后搞定顺序：1. 在 MySQL 命令行中：`SET GLOBAL innodb_file_format = Barracuda;`, `SET GLOBAL innodb_file_per_table = ON;`, `SET GLOBAL  innodb_large_prefix=on;`，修改全局配置；2. 然后删除需要建索引的表后，重建表在最后添加：`ROW_FORMAT = DYNAMIC; – 或COMPRESSED(在CREATE结束时)`

        ```sql
        mysql> show variables like 'innodb_large_prefix';
        +---------------------+-------+
        | Variable_name       | Value |
        +---------------------+-------+
        | innodb_large_prefix | OFF   |
        +---------------------+-------+
        1 row in set (0.00 sec)

        mysql> set global innodb_large_prefix=on; # 修改全局配置
        Query OK, 0 rows affected (0.00 sec)

        mysql> show variables like 'innodb_large_prefix';
        +---------------------+-------+
        | Variable_name       | Value |
        +---------------------+-------+
        | innodb_large_prefix | ON    |
        +---------------------+-------+
        1 row in set (0.00 sec)

        CREATE TABLE `domain` (
        ……
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; # 创建表添加字段
        ```

11. 清空数据表中数据：`delete from [表名];` 或者 `truncate table [表名];`  [TRUNCATE 和 DELETE 的区别](https://blog.csdn.net/stail111/article/details/5718771)不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容；效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据；delete的效果有点像将mysql表中所有记录一条一条删除到删完，保留mysql表的结构；而truncate相当于重新创建了这个表，所有的状态都相当于新表。

        ```sql
        delete from 表名;
        truncate table 表名;
        ```

12. 删除数据库&表：`drop database [库名];` 和 `DROP TABLE [表名];`

        ```sql
        DROP TABLE table_name ;

        mysql> drop database dns;
        Query OK, 6 rows affected (0.36 sec)

        mysql> show databases;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        +--------------------+
        3 rows in set (0.01 sec)
        ```