mysql sets default encoding format utf-8

Preface

  • mysql 5.7.20
  • Demo under mac os

Recently, I was a little idle and wanted to go back to the background, so I installed MySQL database. At first, I logged in to mysql, created users, authorized, created databases, created tables and so on. When I insert tables, I found errors in Chinese insertion of mysql. So I searched the Internet for solutions and found that they were / usr/local/mysql/support-files.Find my-in the directoryDefault.cnforMy.cnfFiles, the strange thing is that neither file exists.

After a while, you find that you only need to create oneMy.cnfThe file is ready, then move to the / etc directory

To configure

  • Create under TerminalMy.cnf

    cd ~
    touch my.cnf
  • editMy.cnf

    open -a xcode my.cnf

    [client]
    default-character-set=utf8
    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci

  • moveMy.cnfTo / etc directory

    mv my.cnf /etc

That's it. Remember to restart mysql. Open MySql under System Preferences and check it after restarting

/usr/local/mysql/bin/mysql -uwz -p

# If.Base_is configuredProfile, then enter mysql-uroot-p
# Enter your root account password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# Re-enter
show variables like '%char%';

+--------------------------+-----------------------------------------------------------+
| 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.20-macos10.12-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.01 sec)

# If prompted, that means it's OK

If you have a my-in your/usr/local/mysql/support-files folderDefault.cnfFile, copy it to your desktop and rename itMy.cnfLast but not least, move to the / etc directory

Change the encoding format of the current table field

After doing this, you find that you still can't insert Chinese, so you can see all the fields in the current table.

# Select Database
use test;
# View table field status
SHOW FULL COLUMNS FROM 'you table';

+----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field    | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id       | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| username | varchar(45)      | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| password | varchar(45)      | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
+----------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.01 sec)

# Change the encoding format of a field
ALTER TABLE `test`.`table_user` CHANGE COLUMN `username` `username` VARCHAR(45) CHARACTER SET 'utf8_unicode_ci' NOT NULL ;

ALTER TABLE `test`.`table_user` CHANGE COLUMN `password` `password` VARCHAR(45) CHARACTER SET 'utf8_unicode_ci' NOT NULL ;

# View table field status
SHOW FULL COLUMNS FROM 'you table';

If the encoding format of the field is utf8_General_If ci, there should be no insertion error. Please check your sql

Tags: MySQL Oracle encoding Database

Posted on Tue, 26 May 2020 13:23:13 -0400 by jaygattis