mysql5.7操作大全

目录

1.表名不区分设置

---

1.表名不区分设置

• 查看大小写设置      

show variables like '%case_table%';

  1代表不区分大小写 0 区分大小写

• 设置

     yum安装my.cnf默认路径在/etc/my.cnf     

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 不区分大小写
lower_case_table_names=1

• 重新查看

mysql> show variables like '%case_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.01 sec)

2. 查看最大连接数

show variables like '%max_connections%';
修改最大连接数
set GLOBAL max_connections = 200;

3. group by 不是全部查询字段

Expression #2 of SELECT list is not in GROUP BY 

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

4. id主键值重新赋值

记得把表里id大的修改成5000(要修改成的id值)以下

alter table table_name auto_increment=5000