MYSQL数据库常用基本设置
2018-12-06 本文已影响13人
梁朋举
问题1：MYSQL数据库字符集设置
当MYSQL插入数据时，出现下面的错误，基本判断是字段的字符集不支持，常用解决方式有2种：
（1）过滤掉非法字符串
content = content.replaceAll("[\\x{10000}-\\x{10FFFF}]", "");
（2）设置MYSQL数据库字符集、表字符集、字段字符集，最好用utf8mb4字符集。
Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x8B\xE6\x9F...' for column 'nickname' at row 1
- 修改数据库字符集：
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
- 修改表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）为新的字符集：
ALTER TABLE tb_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
示例：
[字符集 utf8，排序规则 utf8_general_ci]
ALTER TABLE user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
[字符集 utf8mb4，排序规则 utf8mb4_general_ci]
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4COLLATE utf8mb4_general_ci;
- 修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
示例：
[字符集 utf8，排序规则 utf8_general_ci]
ALTER TABLE user DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- 修改表的字段的字符集：
ALTER TABLE tb_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
示例：
[字符集 utf8，排序规则 utf8_general_ci]
ALTER TABLE user CHANGE name name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
- 查看数据库编码：
SHOW CREATE DATABASE db_name;
- 查看表编码：
SHOW CREATE TABLE tb_name;
- 查看字段编码：
SHOW FULL COLUMNS FROM tb_name;
上述问题建议解决方案：
（1）修改MYSQL配置文件my.ini中的数据库默认编码，并重启数据库
utf8mb4编码有两种常用的排序规则：分别是utf8mb4_general_ci和utf8mb4_unicode_ci，两种排序规则都是为UTF-8字符编码。
utf8mb4_unicode_ci：使用标准的Unicode Collation Algorithm(UCA)，结果更加精确，速度稍慢一些
utf8mb4_general_ci：速度较快
建议使用：utf8mb4_unicode_ci
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
（2）去掉配置文件中 jdbc 连接里面的编码设置
jdbc.url=jdbc:mysql://127.0.0.1:3306/demo
（3）修改数据库表的字符编码设置
(4) 修改对应表的字段的设置
问题2： mysql传输数据过大 max_allowed_packet配置
解决 mysql package for query is too large 错误
mysql根据配置文件会限制server接受的数据包大小，有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。
max_allowed_packet 如果不设置，默认值在不同的 MySQL 版本表现不同，有的版本默认1M，有的版本默认4M。
-
查看目前配置
show VARIABLES like '%max_allowed_packet%'; -
修改方法
（1）修改配置文件，永久性修改（推荐）
linux系统下修改my.cnf，修改配置文件以后，需要重启mysql服务才能生效。
vim /etc/my.cnf
[mysqld]
max_allowed_packet = 100M
（2）命令行方式修改
进入mysql server
在mysql 命令行中运行
set global max_allowed_packet = 20*1024*1024
然后关闭掉这此mysql server链接，再进入。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功
上述方法设置后，虽然会生效，但是会出现过一段时间mysql有自动重置该配置，解决这个问题
mysql 有general_log, 会记录所有执行的sql命令，因为耗费性能，默认是关闭。
打开general_log即可
mysql> set global general_log = ON;
mysql> show variables like 'max_allowed_packet';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set
mysql> show variables like '%log%';
+-----------------------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------------------+--------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | WIN-K6C2K3F78NI.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_mirrored_log_groups | 1 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | .\WIN-K6C2K3F78NI.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| simplified_binlog_gtid_recovery | OFF |
| slow_query_log | ON |
| slow_query_log_file | WIN-K6C2K3F78NI-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+--------------------------+
61 rows in set (0.14 sec)
