mysql创建数据库、用户、授权

创建数据库并指定字符集

##> CREATE DATABASE `rms_dev` CHARACTER SET utf8 COLLATE utf8_general_ci;
> CREATE DATABASE `rms_dev` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
> 
### 查看结果
> show databases

永远不要在MySQL中使用utf8，改用utf8mb4

永远不要在MySQL中使用utf8，改用utf8mb4

修改数据库字符集

## 修改database默认的字符集
mysql>  ALTER DATABASE rms_dev CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

## 查看修改结果
mysql> show create database rms_dev;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| rms_dev  | CREATE DATABASE `rms_dev` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改-表默认字符集

mysql> use rms_dev;
mysql> show tables;  
+-----------------------------+
| Tables_in_rms_dev           |
+-----------------------------+
| tb_version                  |

## 查看当前字符集
mysql> show create table tb_version;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_version | CREATE TABLE `tb_version` (
  `VERSION_CODE` varchar(36) NOT NULL,
  `VERSION_NUM` varchar(128) DEFAULT NULL,
  `VERSION_SYSTEM_NAME` varchar(512) DEFAULT NULL,
  `VERSION_DEVELOP_DATE` datetime DEFAULT NULL,
  `VERSION_ONLINE_DATE` datetime DEFAULT NULL,
  `VERSION_ONLINE_STATE` varchar(32) DEFAULT NULL,
  `CREATE_STAFF` varchar(32) DEFAULT NULL,
  `CREATE_DATE` datetime DEFAULT NULL,
  `UPDATE_STAFF` varchar(32) DEFAULT NULL,
  `UPDATE_DATE` datetime DEFAULT NULL,
  `STATE` varchar(32) DEFAULT NULL,
  `REMARK` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`VERSION_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='版本表'    |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

## 修改表默认字符集
mysql> ALTER TABLE tb_version DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

## 再次执行,查看建表语句 
mysql> show create table tb_version;

修改-列-字符集

## 查看所有列字符集
mysql>  show full columns from tb_version;
+----------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+----------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| VERSION_CODE         | varchar(36)  | utf8_general_ci | NO   | PRI | NULL    |       
| REMARK               | varchar(512) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

## 修改指定列字符集- tb_version.version_code ---  VARCHAR36 为指定的字符串长度
mysql> ALTER TABLE tb_version CHANGE version_code version_code VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改-表默认的字符集 && 所有字符列的字符集

mysql> ALTER TABLE tb_version CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

创建用户

### @localhost 只允许本机访问
 > grant all privileges on rms_dev.* to rms@localhost identified by 'rms2020';

### 允许任意ip访问
###  WITH GRANT OPTION  允许将权限授权给其他用户
### 将rms_dev数据库的所有权限 赋权给rms/rms2020,在任意机器都可登录访问
> grant all privileges on rms_dev.* to rms@'%' identified by 'rms2020'  WITH GRANT OPTION;

## 连接测试
> mysql -urms -prms2020

修改密码

> set password for rms@'%'=password('rms');
> flush privileges;

mysql表名忽略区分大小写

默认情况下，linux下安装的mysql表名是区分大小写的，可以采用如下的方式忽略大小写

### 查看是否区分大小写 
mysql>  show variables like "%case%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

经查看到lower_case_table_names =0,根据如下设置修改：

1. 用ROOT登录，修改/etc/my.cnf
2. 在[mysqld]下加入一行：lower_case_table_names=1
   
3. 重新启动数据库即可: systemctl restart mariadb.service && echo $?

使用的是开源版本 mariadb

---

---

mysql 常见问题

时区问题

表模型
birthdate类型为date

表数据

java代码查询
java对象中获取的时间也正常。

响应报文

使用fastjson转换后，响应的报文与实际时间相差8小时.

mysql设置时区

众所周知，中国所处时区是 东8区，即+8:00。

## 查看mysql时区
mysql>  show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

1. system_time_zone: 系统时区，在MySQL启动时会检查当前系统的时区并根据系统时区设置全局参数system_time_zone的值。
2. time_zone：用来设置每个连接会话的时区。(默认为system时，表示使用system_time_zone的值。)

可以通过如下两种方式解决：

## 1.免重启设置
mysql > set global time_zone='+8:00';
> 登出，重新登录即可  ---仅对当前用户有效

## 2.重启永久生效
# vim /etc/my.cnf  ##在[mysqld]区域中加上
default-time_zone = '+8:00'

---

---

Navicat快捷键

## 查询窗口
CTRL + Q				 	打开查询窗口
CTRL + N					打开新的查询窗口

## 注释
CTRL +  /		 				注释sql语句
CTRL + SHIFT + /  			解除注释

## 执行sql
CTRL + R           				运行查询窗口的sql语句
CTRL + SHIFT + R 	  	只运行选中的sql语句

## 位置切换
HOME							当前行行首
END							当前行行尾
CTRL + HOME  			当前文档首行
CTRL + END				当前文档尾行

## 其他
CTRL + L			删除一行
CTRL + W	      	关闭一个查询窗口
F6               			打开一个mysql命令行窗口

maridb升级

卸载老版本

# rpm -qa|grep mariadb
# yum remove mariadb

# rm -rf /etc/my.cnf
]# rm -rf /var/lib/mysql/

安装高版本

# vim  /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.4 CentOS repository list - created 2020-04-13 14:24 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


# sudo yum install MariaDB-server MariaDB-client

https://downloads.mariadb.org/mariadb/repositories/#distro=CentOS&distro_release=centos7-amd64–centos7&mirror=globotech&version=10.4

docker安装mysql

docker run --name my-mariadb -p 3306:3306 -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mariadb:10.4.12


docker exec -it my-mariadb bash

启动服务出现告警： WARNING: IPv4 forwarding is disabled. Networking will not work.

> vim  /usr/lib/sysctl.d/00-system.conf
   net.ipv4.ip_forward=1

 > systemctl restart network