Mac MySql - 查看以及修改编码格式

登录 - MySQL内部
mysql -u root -p

MySQL内部 - 查看数据库编码格式 （推荐用这个，我用的这个！）
show variables like 'character%';

MySQL内部 - 查看数据库编码格式 （这两个都可以查看数据库编码格式）
show variables like ‘collation%’;

• 按照上面两步我们会输出下面：

mysql> show variables like 'character%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                      |
| character_set_connection | utf8                                                      |
| character_set_database   | latin1                                                    |
| character_set_filesystem | binary                                                    |
| character_set_results    | utf8                                                      |
| character_set_server     | latin1                                                    |
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+

• character_set_database 和 character_set_server 是 latin1 的字符集，也就是说 mysql 后续创建的表都是 latin1 字符集的，不是 utf8，会造成一些麻烦。
• 从以上信息可知数据库的编码为 latin1，需要修改为 gbk 或者是 utf8 ；
  • character_set_client：为客户端编码方式；
  • character_set_connection：为建立连接使用的编码；
  • character_set_database：数据库的编码；
  • character_set_results：结果集的编码；
  • character_set_server：数据库服务器的编码；
  • 只要保证以上五个采用的编码方式一样，就不会出现乱码问题。
  补充：新版本数据库默认编码格式是 utf8mb4，utf8mb4 比 utf8 多了 emoji 编码支持，建议普通表使用 utf8 如果这个表需要支持 emoji 就使用 utf8mb4，也可以全部用 utf8mb4。
• 想要修改编码, 就需要修改 mysql 的配置文件 my.cnf，将需要修改的字段放到配置文件中配置即可。
• 重要问题：在修改 my.cnf 之前一定要关闭 mysql 进程，关闭 mysql，不然会遇到 mysql 的 sock 不能连接的问题!
• 找到下面路径

  /usr/local/mysql/support-files

• 默认情况下安装 Mysql 后不会生成my.cnf 配置文件，但是有些参数需要修改，比如：默认端口、最大连接数、大小写敏感等。
  有些版本安装后可能会在 support-file 文件目录下生成 my-default.cnf 文件，如果有这个文件，可以自己复制该文件，并重命名为 my.cnf，如果没有就需要手动创建了，这边就是自己创建的，文件内容如下，供参考：
  • 简陋版配置文件（如果默认编码格式是 utf8mb4，没特殊情况可以不需要修改编码格式）：

    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    lower_case_table_names = 1
    socket = /tmp/mysql.sock
    port = 3306
    
    [client]
    default-character-set=utf8

  • 字段参考配置文件

    # Example MySQL config file for small systems.
    #
    # This is for a system with little memory (<= 64M) where MySQL is only used
    # from time to time and it's important that the mysqld daemon
    # doesn't use much resources.
    #
    # MySQL programs look for option files in a set of
    # locations which depend on the deployment platform.
    # You can copy this option file to one of those
    # locations. For information about these locations, see:
    # http://dev.mysql.com/doc/mysql/en/option-files.html
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    # The following options will be passed to all MySQL clients
    [client]
    default-character-set=utf8
    #password = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
    # Here follows entries for some specific programs
    # The MySQL server
    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci
    port        = 3306
    socket      = /tmp/mysql.sock
    lower_case_table_names = 1    # 是否对 sql 语句大小写敏感，1 表示不敏感，即不区分大小写
    skip-external-locking
    key_buffer_size = 16K
    max_allowed_packet = 1M
    table_open_cache = 4
    sort_buffer_size = 64K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K
    net_buffer_length = 2K
    thread_stack = 256K   # 该字段根据需要修改，默认是128K，我的是因为启动报了这个字段的错导致启动失败，所以我改成256K了
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (using the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
    server-id   = 1
    # Uncomment the following if you want to log updates
    #log-bin=mysql-bin
    # binary logging format - mixed recommended
    #binlog_format=mixed
    # Causes updates to non-transactional engines using statement format to be
    # written directly to binary log. Before using this option make sure that
    # there are no dependencies between transactional and non-transactional
    # tables such as in the statement INSERT INTO t_myisam SELECT * FROM
    # t_innodb; otherwise, slaves may diverge from the master.
    #binlog_direct_non_transactional_updates=TRUE
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /usr/local/mysql/data
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = /usr/local/mysql/data
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 16M
    #innodb_additional_mem_pool_size = 2M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 5M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    [mysqlhotcopy]
    interactive-timeout

• 保存之后，需要找到 /etc 路径, 将 my.cnf 复制贴贴到 /etc 这个目录下。可以直接通过快捷键 command + shift + G 前面文件:

  /etc

  为什么要放 /etc 下，可以通过命令行查看 mysql 加载的 my.conf 是从哪些目录进行加载的

  $ mysql --verbose --help | grep my.cnf
  
  # 输出（只要符合一个路径就行）
  order of preference, my.cnf, $MYSQL_TCP_PORT,
  /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

  也可以自定义配置文件路径。
• 文件放好之后就重启 mysql，再次查看编码格式

  MySQL内部 - 查看数据库编码格式 （推荐用这个，我用的这个！）
  show variables like 'character%';

  mysql> show variables like 'character%';
  +--------------------------+-----------------------------------------------------------+
  | Variable_name            | Value                                                     |
  +--------------------------+-----------------------------------------------------------+
  | character_set_client     | utf8                                                      |
  | character_set_connection | utf8                                                      |
  | character_set_database   | utf8                                                      |
  | character_set_filesystem | binary                                                    |
  | character_set_results    | utf8                                                      |
  | character_set_server     | utf8                                                      |
  | character_set_system     | utf8                                                      |
  | character_sets_dir       | /usr/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
  +--------------------------+-----------------------------------------------------------+

• 到这里就编码格式就大功告成了！！！