mysql從安裝到建庫，utf8mb4最佳實踐，jdbc連接配接串全解析

我現在用的是mysql5.7,作業系統centos

配置YUM源

下載下傳mysql源安裝包

[root@VM_0_9_centos software]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安裝mysql源

[root@VM_0_9_centos software]# yum localinstall mysql57-community-release-el7-8.noarch.rpm

安裝MySQL

[root@VM_0_9_centos software]# yum localinstall mysql57-community-release-el7-8.noarch.rpm

啟動mysql

systemctl start mysqld

啟動完了之後，檢視mysql狀态

systemctl status mysqld

開機啟動mysql

[root@VM_0_9_centos software]# systemctl enable mysqld
[root@VM_0_9_centos software]# systemctl daemon-reload

修改root本地登入密碼

ysql安裝完成之後，在/var/log/mysqld.log檔案中給root生成了一個預設密碼。通過下面的方式找到root預設密碼，然後登入mysql進行修改：

[root@VM_0_9_centos software]# grep 'temporary password' /var/log/mysqld.log
2018-06-16T08:05:45.944135Z 1 [Note] A temporary password is generated for root@localhost: oOo;3qdZL0sq
shell> mysql -uroot -poOo;3qdZL0sq
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

注意：mysql5.7預設安裝了密碼安全檢查插件（validate_password），預設密碼檢查政策要求密碼必須包含：大小寫字母、數字和特殊符号，并且長度不能少于8位。否則會提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements錯誤*

####添加遠端登入使用者

預設隻允許root帳戶在本地登入，如果要在其它機器上連接配接mysql，必須修改root允許遠端連接配接，或者添加一個允許遠端連接配接的帳戶，為了安全起見，我們添加一個新的帳戶glowd，并且允許所有的ip位址遠端通路：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'glowd'@'%' IDENTIFIED BY 'glowd7^&!'

配置預設編碼為utf8mb4

最新的mysql資料庫預設編碼已經是utf8mb4，這能避免很多不必要的問題。

修改/etc/my.cnf配置檔案,最終的配置如下

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

然後重新開機一下mysql

systemctl restart mysqld

然後登入mysql，檢視是否此時編碼是正确的

[root@VM_0_9_centos software]# mysql -uglowd -p

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

顯示結果如下，即為正确

| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

說明

collation_connection ，collation_database ，collation_server是什麼沒關系。

但必須保證

系統變量                         描述
character_set_client    (用戶端來源資料使用的字元集)
character_set_connection    (連接配接層字元集)
character_set_database    (目前選中資料庫的預設字元集)
character_set_results    (查詢結果字元集)
character_set_server    (預設的内部操作字元集)
這幾個變量必須是utf8mb4。

常見問題解決，以及最佳實踐

建庫-建表

建庫

CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

why utf8mb4

http://www.jianshu.com/p/df1523bc67cd

utf8mb4占4個位元組，可以儲存包括emoji表情在内的很多特殊字元

utf8占3個位元組，可以儲存絕大部分字元

但是為了更好的相容所有此類字元，建議全部使用utf8mb4，避免為未來程式的擴充更新埋坑

why utf8mb4_unicode_ci not utf8_general_ci

https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

在字元校驗上面，utf8_unicode_ci比較準确，utf8_general_ci速度比較快，但是對于現代CPU來說，性能可以忽略。但是對于越來越國際化的我們來說，utf8_unicode_ci更占優勢

why ci not utf8mb4_bin

ci: case insensitive, 即 “大小寫不敏感”, 如果查詢的時候，資料庫中使用者名Glowd，但是使用者輸入glowd，一樣能查出這個使用者。

bin: 将字元串中的每一個字元用二進制資料存儲，區分大小寫

建庫的時候，一定要聲明CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;這個會是建表以及字段的預設字元集以及校驗規則。如果建庫的時候加了，那麼建表和字段的時候都可以不加，預設就是

建表

CREATE TABLE `health_package` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '序号',
  `package_id` int unsigned NOT NULL COMMENT '套系 id',
  `module_id`  int unsigned NOT NULL COMMENT '子產品 id',
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '是否删除，0-未删除，1-删除，預設為0',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time, common column by DB rules',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time,common column by DB rules ',
  PRIMARY KEY (`id`)
)  COMMENT='This table stores module and package of health for ...';

如果建庫的時候加了CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci，在建表或者字段的時候一定不要加了。因為如果你加了DEFAULT CHARSET=utf8mb4，那麼mysql會自動将COLLATE變為utf8_general_ci。這個不是我們所希望的。

為了使用utf8mb4還需要這些條件

對于 JDBC 連接配接，需要使用 MySQL Connector/J 5.1.13（含）以上的版本。
JDBC 的連接配接串中，建議不配置 characterEncoding 選項。後面有解釋
確定 mysql程序character_set_server 參數為 utf8mb4
通過“set names utf8mb4”指令設定會話字元集為 utf8mb4

那麼需要在開啟一個會話的時候，首先執行一個“set names utf8mb4“指令
首先可以在/etc/my.cnf中添加一行配置init_connect='SET NAMES utf8mb4'
或者如果使用的是com.alibaba.druid.pool.DruidDataSource連接配接池，那麼可以加一個屬性<property name="connectionInitSqls" value="set names utf8mb4;"/>

mysql JDBC Driver

mysql JDBC URL格式如下：

jdbc:mysql://[host:port],[host:port]…/database=參數值1[=參數值2]…

現隻列舉幾個重要的參數：

參數名稱	參數說明	預設值	最低版本要求
user	資料庫使用者名（用于連接配接資料庫）	所有版本
password	使用者密碼（用于連接配接資料庫）
useUnicode	是否使用Unicode字元集，如果參數characterEncoding設定為utf-8，本參數值必須設定為true	false	1.1g
characterEncoding	當useUnicode設定為true時，指定字元編碼。比如可設定為gb2312或gbk
autoReconnect	當資料庫連接配接異常中斷時，是否自動重新連接配接？mysql預設連接配接如果超過8小時就會斷開，此參數可以自動重連		1.1
autoReconnectForPools	是否使用針對資料庫連接配接池的重連政策		3.1.3
failOverReadOnly	自動重連成功後，連接配接是否設定為隻讀？	true	3.0.12
maxReconnects	autoReconnect設定為true時，重試連接配接的次數	3
initialTimeout	autoReconnect設定為true時，兩次重連之間的時間間隔，機關：秒	2
connectTimeout	和資料庫伺服器建立socket連接配接時的逾時，機關：毫秒。 0表示永不逾時，适用于JDK 1.4及更高版本	3.0.1
socketTimeout	socket操作（讀寫）逾時，機關：毫秒。 0表示永不逾時

我們通常的jdbc url

jdbc:mysql://localhost:3306/glowd?useUnicode=true&characterEncoding=utf-8&autoReconnect=true

在xml配置檔案中，url中的&符号需要轉義成&。比如在tomcat的server.xml中配置資料庫連接配接池時，mysql jdbc url樣例如下：

jdbc:mysql://localhost:3306/glowd?useUnicode=true&characterEncoding=utf-8&autoReconnect=true

資料庫連接配接參數:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

就是說在jdbc字元串上面，建議不要寫characterEncoding，這樣的話，Connector會自動偵測資料庫的’character_set_server’編碼,現在我們規定character_set_server必須為utf8mb4,那麼就可以略去characterEncoding和useUnicode。現在的jdbc url

jdbc:mysql://localhost:3306/glowd?autoReconnect=true

zeroDateTimeBehavior=convertToNull

JAVA連接配接MySQL資料庫，在操作各項值均為為0（或者有0不正确的資料？？）的timestamp等（日期為0000-00-00。。。。）類型時不能正确處理，而是預設抛出一個異常，比如所見的：java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column XX to TIMESTAMP。

舉個實際的栗子，你的方法中要傳入來自頁面的日期參數值，
按照正常的做法，比如日期值為2016-10-11，但是由于誤操作，傳入了0000-00-00，并沒有設定正确的資料，那麼這時預設抛出java.sql.SQLException異常
（如果設定這一項 zero datetime behavior（英文字面意思為“0datetime反應”）=
convert to null（英文字面意思為“轉化為null”）
，把日期轉換為null代替異常處理）：

即這類操作情況的處理政策，有3種

1.exception（不指定，則預設）---->預設抛出異常，

2.convertToNull------->轉化為null

3.round------->替換成最近的日期即XXXX-01-01

這個在指定管理的資料庫連接配接屬性檔案（jdbc.properties）jdbc的URL常用到,比如這麼寫:

jdbc.url=jdbc:mysql://localhost:3306/databaseName?zeroDateTimeBehavior=convertToNull

mysql從安裝到建庫，utf8mb4最佳實踐，jdbc連接配接串全解析

配置YUM源

下載下傳mysql源安裝包

安裝mysql源

安裝MySQL

啟動mysql

開機啟動mysql

配置預設編碼為utf8mb4

常見問題解決，以及最佳實踐

建庫

why utf8mb4

why utf8mb4_unicode_ci not utf8_general_ci

why ci not utf8mb4_bin

建表

為了使用utf8mb4還需要這些條件

mysql JDBC Driver

繼續閱讀

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method