记一些MySQL比较常用的SQL语句

痛定思痛。 2022-04-03 05:29 293阅读 0赞

> 在这里记录一些平时经常使用的一些SQL，方便以后直接拿来使用。大家也可以在评论里补充，然后我再加进来。

## 自增id的重置 ##

下面的`表名`填需要重置的表

1.  使用truncate：
    
        truncate 表名;
    
     *  说明：使用`truncate`会删除表的数据释放空间，并且重置字自增id，但不会删除表的定义。
     *  用处：需要清空表的时候才能使用。
2.  使用修改表的方法：
    
        alter table 表名 auto_increment = 1
    
     *  说明：此方法和上面的二方法类似。也不会清空数据，有可能会出现重复key的可能，所以此方法也只适用于清空表之后重置自增id或者大量删除后修改自增id。

## 使外键暂时失效、有效 ##

1.  暂时失效
    
        SET FOREIGN_KEY_CHECKS = 0;
2.  外键有效
    
        SET FOREIGN_KEY_CHECKS = 1;

## 设置步长 ##

*  注意：全局，整个系统.
    
        -- 设置步长为5
        SET @@auto_increment_increment = 5;

## 数据库导出为数据字典 ##

*  在工具里面执行下面的SQL，我使用的是`sqlyog`，指定要导出的数据库即可, 这里test为数据库，然后再用`sqlyog`工具导出为Excel表格即可。
    
        -- 把MySQL数据库导出为数据字典
        USE information_schema;
        SELECT
            -- T.TABLE_SCHEMA AS '数据库名称',
            T.TABLE_NAME AS '表名',
                T.TABLE_COMMENT AS '表说明',
            -- T.TABLE_TYPE AS '表类型',
            -- T. ENGINE AS '数据库引擎',
            C.ORDINAL_POSITION AS '字段编号',
            C.COLUMN_NAME AS '字段名',
            C.COLUMN_TYPE AS '数据类型',
            C.IS_NULLABLE AS '允许为空',
            C.COLUMN_KEY AS '键类型',
            C.EXTRA AS '自增属性',
            -- C.CHARACTER_SET_NAME AS '编码名称',
            C.COLUMN_COMMENT AS '字段说明'
        FROM
            COLUMNS C
        INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND C.TABLE_NAME = T.TABLE_NAME
        WHERE
            T.TABLE_SCHEMA = 'test'  -- 指定要生成数据字典的数据库

## 解决MySQL不区分大小写 ##

*  MySQL默认的字符检索策略：`utf8_general_ci`，表示不区分大小写；`utf8_general_cs`表示区分大小写，`utf8_bin`表示二进制比较，同样也区分大小写。
    
     *  建库、建表时区分
        
        创建表时，直接设置表的`collate`属性为`utf8_general_cs`或者`utf8_bin`，像下面这样：
        
            CREATE TABLE 表名(
                id INT PRIMARY KEY,
                name VARCHAR(32) NOT NULL
            ) ENGINE = INNODB COLLATE = utf8_bin;
        
        如果已经创建表，则直接修改字段的`Collation`属性为`utf8_general_cs`或者`utf8_bin`，像下面这样：
        
            ALTER TABLE 表名 MODIFY COLUMN 列名 VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
     *  查询时区分
        
        直接修改sql语句，在要查询的字段前面加上`binary`关键字即可
        
        在每一个条件前加上binary关键字, 下面的`username`和`password`均区分大小写。
        
            select * from user where binary username = 'admin' and binary password = 'admin';
        
        将参数以`binary('')`包围
        
            select * from user where username like binary('admin') and password like binary('admin');
        
        注意：如果你的项目中使用的是hibernate框架的话，使用 `from User where binary username = ? and binary password = ?`会报错, 使用`from User where username like binary(?) and password like binary(?;`才不会报错，hibernate使用的是hql，和原生sql有点区别。

## 创建只读用户 ##

数据库当前只有一个root用户，需要创建一个只读帐户给其他使用，因使用者是使用数据库管理工具在其他主机访问，所以还要开户远程访问权限，操作步骤如下。

1.  使用现有的root用户登录到Mysql。
    
        mysql -uroot -p
2.  创建用户，并授权SELECT查询权限，授权远程访问权限，注意，命令中username/password指用户名密码，请自己指定。若要限制仅指定IP可以使用此用户访问Mysql，将%改为具IP即可。
    
        GRANT SElECT ON *.* TO 'username'@'%' IDENTIFIED BY "password";
3.  刷新mysql权限，使用户创建、授权生效。
    
        flush privileges;
4.  查看MYSQL数据库中所有用户
    
        SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
5.  查看数据库中具体某个用户的权限
    
        how grants for 'cactiuser'@'%';

## 批量修改数据表编码及字符集 ##

1.  修改数据库编码及字符集
    
    这一步比较简单，直接执行即可：
    
        ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
2.  修改数据表与表中字段的编码及字符集
    
    这里需要两步。
    
    首先，需要利用语句，生成所有实际执行的语句：
    
        SELECT
        	CONCAT( "ALTER TABLE `", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" ) AS target_tables 
        FROM
        	INFORMATION_SCHEMA.TABLES 
        WHERE
        	TABLE_SCHEMA = '{数据库名称}' 
        	AND TABLE_TYPE = "BASE TABLE"
    
    此语句会基于 MySQL 的元数据表，得到一组可直接执行的 SQL 列表，如下：
    
        ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
        ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
        ALTER TABLE `table3` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
        ALTER TABLE `table4` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
        ALTER TABLE `table5` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
        ALTER TABLE `table6` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    
    其中，table1 到 table6 即为数据库中的所有数据表。
    
    然后，直接将语句粘贴并执行即可。
    
    注意，这里使用 CONVERT TO 而非 DEFAULT，是因为后者不会修改表中字段的编码和字符集。
    
    此外，对于数据表比较多的数据库，可以先将第一步的执行结果导出到 .sql 文件，再通过该 SQL 文件执行即可。