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

在这里记录一些平时经常使用的一些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 文件执行即可。