MYSQL資料庫常用基本設定

weixin_34321977發表於2018-12-06
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)

相關文章