mysql从安装到建库,utf8mb4最佳实践,jdbc连接串全解析

简介: 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 false 1.1g
autoReconnect 当数据库连接异常中断时,是否自动重新连接?mysql默认连接如果超过8小时就会断开,此参数可以自动重连 false 1.1
autoReconnectForPools 是否使用针对数据库连接池的重连策略 false 3.1.3
failOverReadOnly 自动重连成功后,连接是否设置为只读? true 3.0.12
maxReconnects autoReconnect设置为true时,重试连接的次数 3 1.1
initialTimeout autoReconnect设置为true时,两次重连之间的时间间隔,单位:秒 2 1.1
connectTimeout 和数据库服务器建立socket连接时的超时,单位:毫秒。 0表示永不超时,适用于JDK 1.4及更高版本 0 3.0.1
socketTimeout socket操作(读写)超时,单位:毫秒。 0表示永不超时 0 3.0.1

我们通常的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
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
3807 10
|
SQL Java 关系型数据库
Java连接MySQL数据库环境设置指南
请注意,在实际部署时应该避免将敏感信息(如用户名和密码)硬编码在源码文件里面;应该使用配置文件或者环境变量等更为安全可靠地方式管理这些信息。此外,在处理大量数据时考虑使用PreparedStatement而不是Statement可以提高性能并防止SQL注入攻击;同时也要注意正确处理异常情况,并且确保所有打开过得资源都被正确关闭释放掉以防止内存泄漏等问题发生。
600 13
|
SQL 关系型数据库 MySQL
MySQL数据库连接过多(Too many connections)错误处理策略
综上所述,“Too many connections”错误处理策略涉及从具体参数配置到代码层面再到系统与架构设计全方位考量与改进。每项措施都需根据具体环境进行定制化调整,并且在执行任何变更前建议先行测试评估可能带来影响。
2067 11
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
1103 11
|
SQL 关系型数据库 MySQL
排除通过IP访问MySQL时出现的连接错误问题
以上步骤涵盖了大多数遇到远程连接 MySQL 数据库时出现故障情形下所需采取措施,在执行每个步骤后都应该重新尝试建立链接以验证是否已经解决问题,在多数情形下按照以上顺序执行将能够有效地排除并修复大多数基本链接相关故障。
800 3
|
存储 关系型数据库 MySQL
修复.net Framework4.x连接MYSQL时遇到utf8mb3字符集不支持错误方案。
通过上述步骤大多数情况下能够解决由于UTF-encoding相关错误所带来影响,在实施过程当中要注意备份重要信息以防止意外发生造成无法挽回损失,并且逐一排查确认具体原因以采取针对性措施解除障碍。
868 12
|
存储 SQL 关系型数据库
MySQL中binlog、redolog与undolog的不同之处解析
每个都扮演回答回溯与错误修正机构角色: BinLog像历史记载员详细记载每件大大小小事件; RedoLog则像紧急救援队伍遇见突發情況追踪最后活动轨迹尽力补救; UndoLog就类似时间机器可倒带历史让一切归位原始样貌同时兼具平行宇宙观察能让多人同时看见各自期望看见历程而互不干扰.
718 9
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
432 2
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
692 69

热门文章

最新文章

推荐镜像

更多