MySQL问题汇总（持续更新）

目录

1.设置MySQL（Linux下）数据库的表名大小写敏感的问题

2.远端客户机登陆MySQL受阻：host 192.168.190.1 is not allowed to connect to this mysql server

3.MySQL不支持中文插入的解决（编码问题）

4.MySQL更新表数据时间字段时，另一个时间字段会跟着变

5.表结构、表级列级、DDL查询

---

1.设置MySQL（Linux下）数据库的表名大小写敏感的问题

Linux环境下的MySQL数据库的表名默认是区分大小写的。
(Windows环境下默认是不区分大小写的)

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的。

--1.查看Linux上的MySQL的配置文件/etc/my.cnf：
$ cat /etc/my.cnf
[mysqld]
...
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
...
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
<--此即：配置部分内容>

--2.设置成不区分大小写
--在[mysqld]下面添加一行配置：lower_case_table_names=1 （0区分 | 1不区分）
$ vi /etc/my.cnf
[mysqld]
...
lower_case_table_names=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
...
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
<--此即：配置完内容>

--3.修改完配置之后，一定要重启数据库：
$ service mysqld restart

--**4.修改前，需要对原表数据导出备份，删除原表，修改完成后重新导入。

--5.修改完后，database hive 中的大写表都无法查询。修改不理想。。。

参考链接：

1.linux系统下MySQL表名区分大小写问题

 

2.远端客户机登陆MySQL受阻：host 192.168.190.1 is not allowed to connect to this mysql server

# 使用mysql数据库
  mysql> use mysql;
# 查看用户权限表
  mysql> select host, user, password from user;
# 新增用户权限
  mysql> insert into user(host,user,uassword,ssl_cipher,x509_issuer,x509_subject) value('master','root',password(123456),"","",""); 
*# 另一种更好的授权方式（直接插入有些字段不知道怎么设置，譬如只授权某些操作，而不是所有）
  mysql> grant all privileges on hive.* to root@'192.168.190.1' identified by '123456';
  ：解释：all privileges为所有权限
         on hive.*为数据库hive下的所有表
         '192.168.190.1'为user.host远端IP
         identified by '123456'为设置的登陆密码
# 只授权查询的方式
  mysql> grant select on hive.* to root@'192.168.190.1' identified by '123456';
# 刷新权限表，使立即生效
  mysql> flush privileges;

# user.host 表示：允许用户登陆所使用的客户端IP。（包含以下几种取值）
  %：（通配符）所有主机（包括：远端节点、本地）
  192.168.190.%：IP地址前缀为'192.168.190.'的客户端都允许连接
  localhost：本机
  127.0.0.1：IPv4的本机地址
  ::1：IPv6的本机地址
  master：主机名为master的客户机（可用查看 cat /etc/hosts）

参考链接：1.MySQL之权限管理(mysql.user表详解)

                  2.MySQL中USER表详解

                  3.Host 'XXX' is not allowed to connect to this MySQL server解决方案

3.MySQL不支持中文插入的解决（编码问题）

注1：需要创建数据库之前设置，否则不生效
注2：如果已经创建数据库test，将其删除后再创建，就会生效
1.修改配置文件my.cnf，增加如下字符集编码设置
  $ vi /etc/my.cnf
  [client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  [mysqld]
  character-set-server=utf8
2.重启mysql
  $ service mysqld restart
3.查看编码信息
  mysql> status
  ...
  Server characterset: utf8
  Db     characterset: utf8
  Client characterset: utf8
  Conn.  characterset: utf8
  ...

参考文章：1.MySQL中文编码设置为utf-8  

4.MySQL更新表数据时间字段时，另一个时间字段会跟着变

案例：
表：test  字段：id, time1, time2
执行：update test set time2=current_timestamp() where id=1;后
id=1的这条记录的time1字段也莫名其妙跟着变了？？

原因：
查看表描述：desc test; 发现：
time1 字段的 default为： current_timestamp 
            extra为：on update current_timestamp
也就是time1字段会根据当前时间戳更新

解决：
1.更改表字段定义
  alter table test modify time1 timestamp not null default '0000-00-00 00:00:00'
2.建表时设定
  create table test (
    ...
    time1 timestamp not null default '0000-00-00 00:00:00',
    ...
  )

参考文章：为什么我mysql 用sql语句更新一个时间类型的字段值，另一个时间字段值会变成一样的 

5.表结构、表级列级、DDL查询

# 查看表结构
desc alarms;
# 查看列注释
select column_name, column_comment from information_schema.columns where table_schema ='spark' and table_name = 'alarms' ;
# 查看表注释
select table_name, table_comment from information_schema.tables where table_schema = 'spark' and table_name ='alarms';
# 查看表定义DDL
show create table alarms;