如何将数据库字符集改为中文，让今后所有的数据库都支持中文

最后一行有我自己的my.ini文件

数据库输入中文数据时会变为乱码，

这个时候，我们为每个数据库设置字符集，太过于麻烦，为数据库单独设置重启后又会消失

Set character_set_database=’utf8’;
Set character_set_server=’utf8’;

先查看当前数据库参数

输入代码：

Show variables like ‘char%’;

set character_set_database='utf8';

Show variables like ‘char%’;

为了确保新创建的数据库和表默认使用UTF-8编码，可以在MySQL的配置文件my.cnf或my.ini中添加或修改以下参数：

这样的方式可以让今后所有的数据库新建时默认utf-8

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

 目前只能针对某一数据库来进行，所以直接使用就行

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

通过以上步骤就可以将整个数据库都改为UTF-8支持中文

 my.ini（在Unix/Linux系统中通常称为my.cnf）是MySQL数据库的配置文件，用于指定MySQL服务器的各种设置，包括字符集、端口、数据目录等。根据操作系统和安装方式的不同，这个文件可能位于不同的位置。以下是寻找my.ini或my.cnf文件的一些常见位置：

Windows 系统

1. 默认安装路径：

   • C:\ProgramData\MySQL\MySQL Server X.Y\my.ini 或
   • C:\Program Files\MySQL\MySQL Server X.Y\my.ini

   其中X.Y代表您的MySQL版本号。

2. 通过MySQL Installer安装：如果您使用的是MySQL Installer来安装MySQL，那么配置文件可能会被放置在上述默认位置之一。

3. 自定义路径：如果在安装过程中指定了不同的配置文件路径，则需要参考当时的安装记录或文档。

4. 查看当前使用的配置文件路径：您可以通过以下命令查看MySQL正在使用的配置文件的位置。

   mysql> SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';

   但是更直接的方式是在命令行下运行：

   mysqld --verbose --help | findstr "Default options" -A 10

Unix/Linux 系统

1. 标准位置：

   • /etc/my.cnf
   • /etc/mysql/my.cnf
   • ~/.my.cnf（用户特定配置）

2. 其他可能位置：MySQL服务启动时会按照特定顺序查找配置文件，您可以使用以下命令找到MySQL实际加载的配置文件：

   mysqld --verbose --help | grep -A 1 "Default options"

   这个命令将列出MySQL搜索配置文件的顺序和位置。

3. Docker容器内：如果MySQL是在Docker容器中运行，配置文件可能是通过挂载卷的方式提供的，或者是在容器内部的一个自定义位置。您可以进入容器检查：

   docker exec -it container_name bash

查找配置文件的具体步骤

• Windows:

  1. 打开文件资源管理器。
  2. 导航至上述提到的可能位置之一。
  3. 如果找不到，请尝试使用搜索功能查找my.ini或my.cnf文件。

• Linux/Unix:

  1. 打开终端。
  2. 使用locate或find命令查找配置文件：

     sudo updatedb  # 更新数据库以确保locate能找到最新的文件
     locate my.cnf

     或者

     find / -name my.cnf 2>/dev/null

一旦找到了正确的配置文件，就可以按照之前提供的指南进行编辑以修改字符集设置。记得在修改后重启MySQL服务使更改生效。

以下为我的my.ini全文

# Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows, when MySQL has been installed using MySQL Installer you
# should keep this file in the ProgramData directory of your server
# (e.g. C:\ProgramData\MySQL\MySQL Server X.Y). To make sure the server
# reads the config file, use the startup option "--defaults-file".
#
# To run the server from the command line, execute this in a 
# command line shell, e.g.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# To install the server as a Windows service manually, execute this in a 
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guidelines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]

default-character-set = utf8mb4

# pipe=

# socket=MYSQL

port=3306

[mysql]
no-beep

default-character-set = utf8mb4

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use
# socket=MYSQL

# The access control granted to clients on the named pipe created by the MySQL Server.
# named-pipe-full-access-group=

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# The current server SQL mode, which can be set dynamically.
# Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This 
# makes it easier to use MySQL in different environments and to use MySQL together with other 
# database servers.
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE

general-log=0

general_log_file="DESKTOP-QNC8DKQ.log"

slow-query-log=1

slow_query_log_file="DESKTOP-QNC8DKQ-slow.log"

long_query_time=10

# Error Logging.
log-error="DESKTOP-QNC8DKQ.err"

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
# log-bin

# ***** Group Replication Related *****
# Specifies the server ID. For servers that are used in a replication topology,
# you must specify a unique server ID for each replication server, in the
# range from 1 to 2^32 − 1. "Unique" means that each ID must be different
# from every other ID in use by any other replication source or replica.
server-id=1

# ***** Group Replication Related *****
# Indicates how table and database names are stored on disk and used in MySQL.
# Value 0 = Table and database names are stored on disk using the lettercase specified in the CREATE 
#           TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive. You should not 
#           set this variable to 0 if you are running MySQL on a system that has case-insensitive file 
#           names (such as Windows or macOS). If you force this variable to 0 with 
#           --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames 
#           using different lettercases, index corruption may result.
# Value 1 = Table names are stored in lowercase on disk and name comparisons are not case-sensitive. 
#           MySQL converts all table names to lowercase on storage and lookup. This behavior also applies 
#           to database names and table aliases.
# Value 2 = Table and database names are stored on disk using the lettercase specified in the CREATE TABLE 
#           or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons 
#           are not case-sensitive. This works only on file systems that are not case-sensitive! InnoDB 
#           table names and view names are stored in lowercase, as for lower_case_table_names=1.
lower_case_table_names=1

# This variable is used to limit the effect of data import and export operations, such as 
# those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the 
# LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=151

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache=2000

# Defines the maximum size of internal in-memory temporary tables created 
# by the MEMORY storage engine and, as of MySQL 8.0.28, the TempTable storage 
# engine. If an internal in-memory temporary table exceeds this size, it is 
# automatically converted to an on-disk internal temporary table.
tmp_table_size=51M

#*** MyISAM Specific options
# The maximum size of the temporary file that MySQL is permitted to use while re-creating a 
# MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA). If the file size would be 
# larger than this value, the index is created using the key cache instead, which is slower. 
# The value is given in bytes.
myisam_max_sort_file_size=2146435072

# The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE 
# or when creating indexes with CREATE INDEX or ALTER TABLE.
myisam_sort_buffer_size=92M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=8M

# Each thread that does a sequential scan for a MyISAM table allocates a buffer 
# of this size (in bytes) for each table it scans. If you do many sequential 
# scans, you might want to increase this value, which defaults to 131072. The 
# value of this variable should be a multiple of 4KB. If it is set to a value 
# that is not a multiple of 4KB, its value is rounded down to the nearest multiple 
# of 4KB.
read_buffer_size=128K

# This variable is used for reads from MyISAM tables, and, for any storage engine, 
# for Multi-Range Read optimization.
read_rnd_buffer_size=256K

#*** INNODB Specific options ***
# innodb_data_home_dir=

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size in bytes of the buffer that InnoDB uses to write to the log files on 
# disk. The default value changed from 8MB to 16MB with the introduction of 32KB 
# and 64KB innodb_page_size values. A large log buffer enables large transactions 
# to run without the need to write the log to disk before the transactions commit. 
# Thus, if you have transactions that update, insert, or delete many rows, making 
# the log buffer larger saves disk I/O.
innodb_log_buffer_size=16M

# The size in bytes of the buffer pool, the memory area where InnoDB caches table 
# and index data. The default value is 134217728 bytes (128MB). The maximum value 
# depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems 
# and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU 
# architecture and operating system may impose a lower practical maximum size than the 
# stated maximum. When the size of the buffer pool is greater than 1GB, setting 
# innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on 
# a busy server.
innodb_buffer_pool_size=128M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=48M

# Defines the maximum number of threads permitted inside of InnoDB. A value 
# of 0 (the default) is interpreted as infinite concurrency (no limit). This 
# variable is intended for performance tuning on high concurrency systems.
# InnoDB tries to keep the number of threads inside InnoDB less than or equal to 
# the innodb_thread_concurrency limit. Once the limit is reached, additional threads 
# are placed into a "First In, First Out" (FIFO) queue for waiting threads. Threads 
# waiting for locks are not counted in the number of concurrently executing threads.
innodb_thread_concurrency=17

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load

# The TCP/IP Port the MySQL Server X Protocol will listen on.
# loose_mysqlx_port=33060