Modifying Coding Profile in MySQL

Install MySQL Service

Under Linux, you can install it directly with the command line sudo apt-get install mysql-server of Linux. During the installation, a session box will pop up, prompting the user to enter the root password of MySQL. The password here must be filled in, otherwise MySQL will randomly generate a password.

If we are not prompted to enter the root password during the installation process, which is the default user and password assigned to us by MySQL, we need to find the default user name and password of MySQL. Under Ubuntu, the default user name and password of MySQL are in the file / etc/mysql/debian.cnf.

Check the username and password: sudo cat debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = SR6s5J9qLPYciec6
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = SR6s5J9qLPYciec6
socket   = /var/run/mysqld/mysqld.sock

Log in using user and password

mysql -udebian-sys-maint -pSR6s5J9qLPYciec6

Modify root password

After using MySQL default username and password to login to MySQL, you can modify the root password to facilitate future access to MySQL.

• Modify commands:

update mysql.user set authentication_string=password('newpassword') where user='root';

Successful creation will prompt: Query OK, 0 rows affected, 1 warning (0.26 sec)

• Refresh permission list

flush privileges;

Then you can login happily!

Modify Character Set

The default encoding of MySQL under Linux is latin1. If we save Chinese characters, the encoding format will be set when we create the database. For example:

myslq> CREATE DATABASE `test` CHARSET=uft8;

This allows you to set the encoding, but it's very inconvenient. If you forget carelessly, you have to rewrite it.

MySQL provides configuration files that can be modified to set the encoding.

1. First look at the MySQL character encoding set and proofreading set

# Coding set
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
# Proofreading set
mysql> SHOW VARIABLES LIKE '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+

2. Exit MySQL and stop MySQL service

MySQL Service Command:

• sudo service mysql start: Start the service
• sudo service mysql stop: Close the service
• sudo service mysql status: View service status
• sudo service mysql restart: restart service

3. Modify MySQL configuration file

• File location: / etc/mysql/my.cnf
• Edit file: sudo vim/etc/mysql/my.cnf
• Add something at the end of the file

[client]
default-character-set = utf8

[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci

• Save exit

4. Start up the service

• sudo service mysql start

5. Enter MySQL to view the database character set again

mysql> SHOW VARIABLE 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE '%colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

When the modification is completed, the character set will not be modified in the first way.

The difference between utf8 and utf8mb4 encoding in MySQL

After MySQL 5.x, add a utf8mb4 encoding. What's the difference between utf8mb4 and the original uft8? MySQL document It turns out that utf8mb4 is a superset of utf8, and UTF8 in MySQL is a historical legacy.

Originally UTF-8 was a variable-length character encoding that could use 1 to 4 bytes to encode Unicode. But UTF-8 in MySQL can only use 3 bytes to encode. For BMP characters, utf8 and utf8mb4 are not very different, because three bytes are enough for BMP character encoding.

But for supplementary character, utf8 in MySQL cannot be fully stored, because for these extended characters, 3 bytes cannot be fully coded. uft8mb4 encoding is required.