mysql 设置max all_MYSQL数据库常用基本设置

问题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)