MySQL学习笔记(一)

一、MySQL基础命令

1、关系型MySQL在互联网行业广泛使用，目前主流版本有5.7.x和8.0.x,企业使用5.7.x的较多一些，8.0.x性能提升较高，逐渐有很多公司用起来8.0.x版本了。本文以mysql5.7.29版本总结。依持久层框架如MyBatis/MyBatisPlus、JPA、Hibernate、JdbcTemplate等都对原始JDBC操作MySQL实现增删改差都提供了高度封装，上述框架也是目前企业开发使用最多的了。

2、Java连接MySQL的驱动选择：(mysql5.7的版本是可以兼容)

①:直接使用mysql-connector-java 5.1.x版本即可，驱动名称：com.mysql.jdbc.Driver，以及配置Druid连接池参数等。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

②:使用mysql -connector-java 8.0.x版本(5.1.x版本存在高危安全漏洞，看公司的安全规范了,)

驱动名称：com.mysql.cj.jdbc.Driver.

连接url:url: jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8（使用mysql8的驱动必须要指定时区,否则连接报错！）

注意时区问题：

serverTimezone=GMT%2B8
北京时间东八区
serverTimezone=UTC

UTC代表的是全球标准时间，但是我们使用的时间是北京时区也就是东八区，领先UTC八个小时。比如使用UTC时间，我们在Java代码中指定了时间，但是插入到数据库后的时间要少了8小时，因此我们直接采用东八区时间即可。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>

③：如果考虑到软件权限License,也可以使用mariaDB(mysql的一个分支版本)，测试兼容mysql5.7.x的。

驱动名称：org.mariadb.jdbc.Driver

连接URL：jdbc:mariadb://localhost:3306/XXX

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.6.0</version>
</dependency>

3、配置命令部分(不涉及常用的增删改查)

①：查看mysql版本

select version();

②：mysql字符集查看

mysql数据库字符集包括字符集和校对规则两个概念，字符集定义mysql字符串的存储方式，校对规则则是定义比较字符串的方式。

常用字符集包括：GBK、UTF8、UTF8mp4。GBK占用2个字节，UTF8占3个字节，UTF8mp4是UTF8的超集，占用4个字节，mysql8版本已默认为UTF8mp4了。建议UTF8mp4,这里全部改为UTF8mp4。解决常见讨厌的中文乱码问题！

配置文件[mysqld]下面修改：character-set-server=utf8mp4。[mysql] 用于配置客户端参数。

3、mysql的连接数查看

show variables like 'max_connections';（默认是151个），线上根据业务情况设置，并不是越大越好，尽量降低事务占用连接的耗时，减低连接持有时间。

4、mysql表碎片查看

mysql表碎片产生原因：mysql删除数据不是真正意义上的物理删除(删除完数据就释放表空间),是修改标志位的逻辑删除方式,只是将数据文件的标志位删除了，也没有整理数据文件，因此不会彻底释放表空间的，删除数据后的表空间后面，就会留下碎片，重复插入数据的部分效率要比正常存储空间低一些。

计算常用公式：

碎片大小=数据总大小-实际表空间大小

实际表空间大小=数据大小(data_length)+索引大小(index_length)

实际表空间大小文件大小=rows*avg_row_length

show table status like '%user%'\G(单位是B，转换MB方式/1024/1024)

5、mysql的表的自增ID

①：创建表指定自增ID（无符号整型最大占：4字节，最大2^32-1=4294967295）

图片来自菜鸟教程mysql

create table `test1` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967294;

②：查看指定表的自增ID的SQL如下：

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '指定数据库'
AND   TABLE_NAME   = '指定数据表';

1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段

当如果某次要插入的值是X，当前的自增值是Y时：

1.如果X

2、如果X>=Y，就需要把当前自增值修改为新的自增值;(结合上一张图对比哈)

③：查看mysql的自增步长和初始值

 SHOW VARIABLES LIKE 'auto_inc%';

④：自增值主键用完了后会报主键冲突。

原因：InnoDB指定了表自增主键后再插入数据的时候会获取当前插入表的自增主键，第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误。

⑤：mysql自增ID不连续，只是递增的

1、唯一键冲突

2、事务回滚得方式

附：推荐两款数据库管理工具。

①：DataGrip

支持众多主流数据库。

②：HeidiSQL【官网直接下载】下载，无需破解直接使用，很方便.

③、IDEA内的database插件

mysql 5.7的默认的4个数据库

①、information_schema：保存了MySQl服务所有数据库的信息，具体MySQL服务有多少个数据库，各个数据库有哪些表，各个表中的字段是什么数据类型，各个表中有哪些索引，各个数据库要什么权限才能访问。

②、mysql

保存MySQL的权限、参数、对象和状态信息。

如哪些user可以访问这个数据、DB参数、插件、主从等。

③、performance_schema

主要用于收集数据库服务器性能参数

提供进程等待的详细信息，包括锁、互斥变量、文件信息；

④、sys

Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

【附录】常用数据库枚举类

参考MyBatisPlus的枚举类.

public enum DbType {
    /**
     * MYSQL
     */
    MYSQL("mysql", "MySql数据库"),
    /**
     * MARIADB
     */
    MARIADB("mariadb", "MariaDB数据库"),
    /**
     * ORACLE
     */
    ORACLE("oracle", "Oracle数据库"),
    /**
     * DB2
     */
    DB2("db2", "DB2数据库"),
    /**
     * H2
     */
    H2("h2", "H2数据库"),
    /**
     * HSQL
     */
    HSQL("hsql", "HSQL数据库"),
    /**
     * SQLITE
     */
    SQLITE("sqlite", "SQLite数据库"),
    /**
     * POSTGRE
     */
    POSTGRE_SQL("postgresql", "Postgre数据库"),
    /**
     * SQLSERVER2005
     */
    SQL_SERVER2005("sqlserver2005", "SQLServer2005数据库"),
    /**
     * SQLSERVER
     */
    SQL_SERVER("sqlserver", "SQLServer数据库"),
    /**
     * DM
     */
    DM("dm", "达梦数据库"),
    /**
     * UNKONWN DB
     */
    OTHER("other", "其他数据库");

    /**
     * 数据库名称
     */
    private final String db;
    /**
     * 描述
     */
    private final String desc;

    /**
     * 获取数据库类型（默认 MySql）
     *
     * @param dbType 数据库类型字符串
     */
    public static DbType getDbType(String dbType) {
        DbType[] dts = DbType.values();
        for (DbType dt : dts) {
            if (dt.getDb().equalsIgnoreCase(dbType)) {
                return dt;
            }
        }
        return OTHER;
    }
}

更多推荐

MySQL学习笔记(一)