Welcome to pay attention to github.com/hsfxuebao, I hope to help you, if you think it can trouble to click on the Star ha

1. Instructions before installation

1.1 Preparing The Linux System and Tools

• Install and start two VMS: CentOS 7
• Clone a VM
  • The MAC address
  • The host name
  • The IP address
  • UUID
• Xshell and Xftp are installed to access CentOS
• The difference between CentOS6 and CentOS7 in MySQL usage

Firewall: 6 is iptables, 7 is Firewalld command to start the service, 6 is service, and 7 is systemctl

1.2 Checking whether MySQL has been installed

• If you are installing using RPM, check the RPM PACKAGE:

The RPM - qa | grep -i mysql # - I ignore caseCopy the code

• Check mysql service:

systemctl status mysqld.service
Copy the code

• If an older version of mysql-libs package exists, the following output is displayed:
• If the mysql-lib version does not exist, the following information is displayed:

1.3 Uninstalling MySQL

1. Disabling the mysql service

systemctl stop mysqld.service
Copy the code

2. Check the current mysql installation status

The RPM - qa | grep -i mysql # or yum list installed | grep mysqlCopy the code

3. Uninstall the installed programs displayed in the preceding commands

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
Copy the code

Be sure to uninstall clean, executed repeatedly RPM – qa | grep -i mysql to confirm whether there is a discharge residues

4. Delete files related to mysql

Find relevant files

find / -name mysql
Copy the code

Delete the files found by the preceding command

rm -rf xxx
Copy the code

5. Delete my. CNF

2. Install MySQL in Linux

2.1 Four major versions of MySQL

MySQL Community Server is an open-source, free, downloadable version of the MySQL Community Server. MySQL Enterprise Edition Enterprise Edition, for a fee, cannot be downloaded online and can be tried for 30 days. Provides more functions and more complete technical support, more suitable for the database function and reliability requirements of enterprise customers. MySQL Cluster Cluster edition, open source, free. Set up cluster Server, can be several MySQL Server package into a Server. It needs to be used on a community or enterprise basis. MySQL Cluster CGE Advanced Cluster edition for a fee.

MySQL Workbench (GUITOOL) is an ER/ database modeling tool designed specifically for MySQL. It is the successor to the well-known database design tool DBDesigner4. MySQLWorkbench is divided into two versions, namely community edition (MySQLWorkbench OSS) and commercial edition (MySQL WorkbenchSE).

2.2 Downloading the specified MySQL version

1. Download address

Official: www.mysql.com/

2. Open the official website and clickDOWNLOADS

Then, click MySQL Community(GPL) Downloads

3. Click MySQL Community Server

4. Select the appropriate version in General Availability(GA) Releases

If you want to install MySQL in Windows, download the MSI installation program. Click the Go to Download Page to Download:

• There are two installation programs for MySQL on Windows
  • Mysql -installer-web-community-8.0.25.0.msi mysql- Installer-web-community-8.0.25.0. msi Components must be installed on a network during installation.
  • Mysql-installer-community-8.0.25.0. msi mysql-installer-community-8.0.25.0.msi You can install it offline. Recommend it.

5. Install MySQL in Linux

5.1 Software Installation In Linux:

Mode 1: RPM Command Run the RPM command to install the software package whose extension name is. RPM. General format of the. RPM package:

Mode 2: Install the yum command using the yum source obtained from the Internet.

Method 3: Compile and install the source package for the compressed format such as tar.gz, use the tar command to decompress. If it is a different compression format, use a different command.

5.2 Installing MySQL in Linux There are various installation methods

installation	The characteristics of
rpm	Simple installation, poor flexibility, unable to flexibly choose the version, upgrade
rpm repository	The installation package is very small, the version installation is simple and flexible, easy to upgrade, need to be installed online
Universal binary package	More complex installation, high flexibility, good versatility of the platform
The source code package	The most complex installation, long time, flexible parameter Settings, good performance

• Here can not directly choose CentOS 7 version of the system, and so the selection and the matching Red Hat Enterprise Linux |
• Downloads.mysql.com/archives/co… Bundle Full package. Includes all of the following components. You don’t need to download them one by one.

6. Use a compression tool to open the downloaded tar package

RPM installation package after decompression (extracted installation package in red box)

2.3 Checking MySQL dependencies in CentOS7

1. Check/TMP temporary directory permissions (essential)

During the mysql installation, the mysql user will create a tmp_db file in the/TMP directory. Therefore, grant/TMP greater permissions. Perform:

chmod -R 777 /tmp
Copy the code

2. Check dependencies before installation

rpm -qa|grep libaio
Copy the code

If there is a libaio package as follows:

rpm -qa|grep net-tools
Copy the code

If the following nettools package exists:

rpm -qa|grep net-tools
Copy the code

If no, install RPM in the centos installation disk. If you install Linux with a graphical interface, this is all installed.

2.4 installing MySQL in CentOS7

1. Copy the installation program to the /opt directory

Run in mysql installation directory :(must run in sequence)

RPM RPM -ivh mysql-community-common-8.0.25-1.el7.x86_64. RPM RPM -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64. RPM RPM RPM -ivh mysql-community-libs-8.0.25-1. El7.x86_64. RPM RPM -ivh mysql-community-client-8.0.25-1 Mysql - community - server - 8.0.25-1. El7. X86_64. RPMCopy the code

• Mysql > install mysql-community-server mysql > install mysql-community-server mysql > install mysql-community-server
• RPM is short for Redhat Package Manage. Through RPM management, users can Package source code into files with RPM extension, which is easy to install.
• -i, –install Install software package
• -v, –verbose Displays more detailed information
• -h, — Lists hash marks when installing the hash package (used with -v for better results) and displays the progress bar

2. Screenshot of the installation process

Possible error messages during installation

Yum remove mysql-libs yum remove mysql-libs

3. Check the MySQL version

Run the following command to install mysql successfully. For example, if you type the version information, such as java-version

Mysql --version # mysql --versionCopy the code

Run the following command to check whether the installation is successful. You need to add -i, you don’t need to be case-sensitive, otherwise you won’t be able to search.

rpm -qa|grep -i mysql
Copy the code

4. Service initialization

To ensure that the owner of the database directory and files is the mysql login user, if you are running the mysql service as root, you need to initialize the mysql service by executing the following command:

mysqld --initialize --user=mysql
Copy the code

Note: – If the Initialize option is set to Security mode by default, a password is generated for the root user and marked as expired. After login, you need to set a new password. The generated temporary password is recorded in the history.

View password:

cat /var/log/mysqld.log
Copy the code

root@localhost: Initializes the password

5. Start the MySQL database and check the MySQL status

Service: systemctl stop mysqld.service: systemctl stop mysqld.service: systemctl stop mysqld.service: systemctl stop mysqld.service Systemctl restart mysqld.service Run the systemctl status mysqld.service command to restart mysqld.serviceCopy the code

The mysqld executable represents the MySQL server program, and running this executable directly starts a server process.

View the process:

ps -ef | grep -i mysql
Copy the code

6. Check whether the MySQL service starts automatically

systemctl list-unit-files|grep mysqld.service
Copy the code

The default is Enabled.

• If it is not enabled, run the following command to enable the automatic startup

systemctl enable mysqld.service
Copy the code

• To disable the automatic startup, run the following command

systemctl disable mysqld.service
Copy the code

3. The MySQL login

3.1 Initial Login

Run the mysql -hlocalhost -p3306-uroot -p command to log in and Enter the initial password at Enter password

3.2 Changing a Password

Because the initial password is expired by default, the database will display an error to change the password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Copy the code

After version 5.7 (excluding 5.7), mysql has added a new password security mechanism. Setting a new password is too simple and will cause an error.

After changing to more complex password rules, the Settings are successful and the database can be used normally

3.3 Configuring Remote Login

1. Current issues

SQLyog/Navicat/SQLyog/Navicat/SQLyog/Navicat

2. Confirm the network

1. On the remote machine, run the ping IP address command to ensure that the network is normal. 2

Telnet IP address Port numberCopy the code

Extension: Telnet command to enable:

3. Disable the firewall or open the port

Method 1: Disable the firewall

• CentOS6:

service iptables stop
Copy the code

• CentOS7

Systemctl start firewald. service systemctl status firewald. service systemctl stop firewald. service Systemctl enable firewalld. Service systemctl disable firewalld. ServiceCopy the code

Method 2: Open ports

View open port numbers

firewall-cmd --list-all
Copy the code

Set an open port number

firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
Copy the code

Restarting the Firewall

firewall-cmd --reload
Copy the code

4. Modify the configuration in Linux

MySQL > MySQL

use mysql;
select Host,User from user;
Copy the code

You can see that the current host configuration of user root is localhost.

• Change Host to wildcard %

The Host column specifies the IP address that the user is allowed to log in to, for example, user=root Host=192.168.1.1. The root user can only be accessed from the 192.168.1.1 client. User =root Host=localhost: Indicates that the user can access the IP address only through the local client. % is a wildcard character. If Host=192.168.1.%, it means that any client whose IP address prefix is 192.168.1. If Host=%, all IP addresses have connection permission. Note: In a production environment, you cannot set host to % for ease of use. This may cause security problems. The specific Settings can be set according to the IP address of the production environment.

update user set host = '%' where user ='root';
Copy the code

Setting Host to % allows remote access.

After modifying Host, run Flush PRIVILEGES for the Settings to take effect immediately

flush privileges;
Copy the code

5. Test

If it is MySQL5.7, then you can successfully connect to MySQL using SQLyog or Navicat. If MySQL8.0 is used, the following problems will occur during connection

Error 2058: mysql > create new connection Mysql -u root -p mysql -u root -p mysql -u root -p

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';
Copy the code

Then reconfigure the connection to SQLyog, then the connection is successful, OK.

4. MySQL8 password strength evaluation

4.1 Setting Passwords for different MySQL Versions (Possible)

MySQL5.7: Succeeded

mysql> alter user 'root' identified by 'abcd1234';
Query OK, 0 rows affected (0.00 sec)
Copy the code

MySQL8.0: failed

mysql> alter user 'root' identified by 'abcd1234';  # HelloWorld_123
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
Copy the code

4.2 Security Policies before MySQL8

Before MySQL 8.0, MySQL used the validate_password plug-in to check and verify account password strength to ensure account security. Method 1: Add parameters to the my.cnf parameter file

[mysqld] plugin-load-add=validate_password.so #ON/OFF/FORCE/FORCE_PLUS_PERMANENT: Validate-password =FORCE_PLUS_PERMANENTCopy the code

Note 1: The suffix of the validate_password file name in plugin Library varies from platform to platform. The file name extension is.so for Unix and UNIX-like systems, and.dll for Windows. Note 2: You must restart the MySQL service for the parameters to take effect. Note 3: The FORCE_PLUS_PERMANENT parameter is used to prevent plug-ins from being uninstalled while MySQL is running. An error is reported when you uninstall the plug-in. As shown below.

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_NAME = 'validate_password'; +-------------------+----------------------+---------------+----------------------+ | PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION | +-------------------+----------------------+---------------+----------------------+ | validate_password | validate_password.so | ACTIVE | FORCE_PLUS_PERMANENT | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > UNINSTALL PLUGIN validate_password; ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be unloaded mysql>Copy the code

Install/Enable Plug-in Approach 2: Run time command Installation (recommended)

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.11 sec)
Copy the code

This method is also registered in the metadata, the mysql.plugin table, so there is no need to worry that the plug-in will fail when mysql restarts.

4.3 MySQL8 Security Policy

1. The validate_password instructions

MySQL 8.0 introduced the server Components feature. The Validate_password plug-in has been re-implemented with server Components. In databases 8.0.25, the Validate_password component is automatically installed by default.

Before the plug-in is installed, execute the following two commands. The execution effect is as follows:

mysql> show variables like 'validate_password%'; Empty set (0.04 SEC) mysql> SELECT * FROM mysql.com; ERROR 1146 (42S02): Table 'mysql.component' doesn't existCopy the code

After installing the plug-in, execute the following two commands, and the execution effect is as follows:

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn           |
+--------------+--------------------+------------------------------------+
|       1 |          1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)
Copy the code

mysql> show variables like 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 7 rows in the set (0.01 SEC)Copy the code

About the system variables corresponding to the VALIDATE_PASSWORD component:

options	The default value	Parameters to describe
validate_password_check_user_name	ON	If this parameter is set to ON, the password can be set to the current user name.
validate_password_dictionary_file		The pathname of the dictionary file used to check passwords, empty by default
validate_password_length	8	Minimum password length, that is, the password length must be greater than or equal to 8
validate_password_mixed_case_count	1	If the password policy is medium or stronger
validate_password_number_count	1	Number of digits that must be included in the password
validate_password_policy	MEDIUM	The password strength verification level can be specified using the values 0, 1, or 2 or the corresponding symbol values LOW, MEDIUM, or STRONG. 0/LOW: Only the length is checked. 1/MEDIUM: Check the length, digits, case, and special characters. 2/STRONG: checks the length, digits, case, special characters, and dictionary files.
validate_password_special_char_count	1	Number of special characters that a password must contain

Tip: Default values for components and plug-ins may differ. For example, the default value of MySQL 5.7.VALIDate_password_check_user_name is OFF.

2. Modify the security policy

Example Change the password authentication security strength

SET GLOBAL validate_password_policy=LOW; SET GLOBAL validate_password_policy=MEDIUM; SET GLOBAL validate_password_policy=STRONG; SET GLOBAL validate_password_policy=0; # For LOW SET GLOBAL validate_password_policy=1; # For MEDIUM SET GLOBAL validate_password_policy=2; SQL = set global validate_password_policy=LOWCopy the code

In addition, you can change the length of the characters in the password

set global validate_password_length=1;
Copy the code

3. Password strength test

If you create a password and encounter “Your password does not satisfy the current policy requirements”, you can use the function component to check whether the password meets the condition: 0-100. A score of 100 indicates the use of the most basic rules: uppercase + lowercase + special characters + digits password of more than 8 characters

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium'); +--------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('medium') | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | + 25 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5'); +-------------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 100 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec)Copy the code

Note: This function always returns 0 if the Validate_password component or plug-in is not installed. About the password complexity policy. As shown in the following table:

Password Test	Return Value
Length < 4	0
Length ≥ 4 and < validATE_password. Length	25
Satisfies policy 1 (LOW)	50
Satisfies policy 2 (MEDIUM)	75
Satisfies policy 3 (STRONG)	100

4.4 Uninstalling Plug-ins and Components (Understanding)

Unload the plugin

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Copy the code

Uninstall the component

mysql> UNINSTALL COMPONENT 'file://component_validate_password'; Query OK, 0 rows affected (0.02sec)Copy the code

5. Operations related to character sets

5.1 Modifying the MySQL5.7 Character Set

1. Modify the procedure

Before MySQL 8.0, the default character set was latin1 and the UTF8 character set pointed to UTF8MB3. Web developers often change the encoding to the UTF8 character set during database design. If you forget to change the default encoding, garbled characters can occur. Starting with MySQL8.0, the default encoding of the database will be changed to UTF8MB4 to avoid the above garbled problem. Operation 1: View the default character set

show variables like 'character%';
# 或者
show variables like '%char%';
Copy the code

MySQL8.0:

MySQL5.7 default client and server use latin1, do not support Chinese, save Chinese error. MySQL5.7:

Error: Adding Chinese data to MySQL5.7

Because by default, latin1 is used to create tables. As follows:

Operation 2: Modify the character set

vim /etc/my.cnf
Copy the code

In MySQL5.7 or earlier, add the Chinese character set configuration at the end of the file:

character_set_server=utf8
Copy the code

Operation 3: Restart the MySQL service

systemctl restart mysqld
Copy the code

However, the setting of the original database and the original table will not change, and parameter modification only takes effect for the newly created database.

2. Changes to existing library and table character sets

In MySQL5.7, the database created previously is latin1.

Modify the character set of the created database

alter database dbtest1 character set 'utf8';
Copy the code

Modify the character set of the created table

alter table t_emp convert to character set 'utf8';
Copy the code

Note: If the original data is not ‘UTF8’, the data encoding itself will not change. The existing data needs to be differentiated

Out or delete and then reinsert.

5.2 Character sets at all levels

MySQL has 4 levels of character sets and comparison rules:

• Server level
• Database level
• Table level
• Column level

Execute the following SQL statement:

show variables like 'character%';
Copy the code

• Character_set_server: Character-level character set
• Character_set_database: Character set of the current database
• Character_set_client: Character set used by the server to decode the request
• Character_set_connection: The request string is converted from character_set_client when the server processes the request
• character_set_connection
• Character_set_results: Character set used by the server to return data to the client

1. Server level

Character_set_server: Character-level character set. We can change the values of these two variables by starting the server program with the startup option or by using the SET statement while the server program is running. For example, we could write this in the configuration file:

[server] Character_set_server = GBK # Collation_server = GBk_chinese_ci #Copy the code

The values of these two system variables are changed after the configuration file is read during server startup.

2. Database level

Character_set_database: Character set of the current database The character_set_DATABASE character set and comparison rules can be specified when creating or modifying a database. The syntax is as follows:

CREATE DATABASE DATABASE name [[DEFAULT] CHARACTER SET name] [[DEFAULT] COLLATE comparison rule name]; ALTER DATABASE DATABASE name [[DEFAULT] CHARACTER SET name] [[DEFAULT] COLLATE comparison rule name];Copy the code

3. The table level

We can also specify the character set and comparison rules of the table when creating or modifying the table. Syntax:

[[DEFAULT] CHARACTER SET CHARACTER SET [COLLATE comparison rule name]] ALTER TABLE [[DEFAULT] CHARACTER SET CHARACTER SET name] [COLLATE comparison rule name]Copy the code

** If the character set and comparison rule are not specified in the statement for creating or modifying a table, the character set and comparison rule of the database where the table resides are used as the character set and comparison rule of the table. **

4. The column level

For columns that store strings, different columns in the same table can also have different character sets and comparison rules. When creating or modifying a column definition, you can specify the character set and comparison rules for the column as follows:

CREATE TABLE TABLE name (column name string type [CHARACTER SET CHARACTER SET name] [COLLATE comparison rule name], other columns... ; ALTER TABLE TABLE name MODIFY column name String type [CHARACTER SET CHARACTER SET name] [COLLATE comparison rule name];Copy the code

For a column, if the character set and comparison rules are not specified in the statement created or modified, the character set and comparison rules of the table in which the column is located are used as the character set and comparison rules for the column.

When converting a column character set, note that an error may occur if the data stored before the conversion cannot be represented by the converted character set. For example, if the column used UTF8 and stored some Chinese characters, converting the column to ASCII will cause an error, because ASCII does not represent Chinese characters.

5. Summary

The associations between the four character sets and the comparison rules are as follows:

• ifCreate or modify columnsWhen no explicit character set and comparison rules are specifiedTable by defaultCharacter sets and comparison rules
• ifCreate tableIf no explicit character set and comparison rules are specifiedThe default is databaseCharacter sets and comparison rules
• ifWhen creating a databaseNo explicit character set and comparison rules are specified for the databaseThe default is serverCharacter sets and comparison rules

With these rules in mind, for a given table, we should know what the character set and comparison rules are for each column, so that we can determine how much storage space each column will actually occupy when storing data based on the type of the column. Let’s say we insert a record into table T

Mysql > INSERT INTO t(col) VALUES(' we '); Query OK, 1 row affected (0.00 SEC) mysql> SELECT * FROM t; + -- -- -- -- -- -- -- -- + | s | + -- -- -- -- -- -- -- -- + | we | + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

The character set used by col is GBK. A character ‘I’ encoded in GBK is 0xCED2, occupying two bytes. The actual data of two characters occupies four bytes. If you change the column’s character set to UTF8, these two characters actually take up 6 bytes

5.3 Character Sets and Comparison Rules (Understanding)

1. The utf8 and utf8mb4

The UTF8 character set indicates that a character needs to use 1 to 4 bytes, but some commonly used characters can use 1 to 3 bytes. The character set represents the maximum length of a character, which in some ways affects the storage and performance of the system. Therefore, the designers of MySQL secretly defined two concepts:

• utf8mb3: Castratedutf8Character set: contains 1 to 3 bytes.
• utf8mb4: authenticutf8Character set: contains 1 to 4 bytes.

2. Compare rules

In the table above, MySQL version supports 41 character sets. The Default Collation column indicates the Default comparison rule in this character set, including which language the comparison rule applies to. For example, UTF8_polish_CI indicates the comparison rule in Polish. Utf8_spanish_ci is a Spanish comparison rule, and UTF8_general_CI is a general comparison rule. Suffixes indicate whether the comparison rules discriminate between accent and case in languages. Details are as follows:

The suffix	English interpretation	describe
_ai	accent insensitive	There is no distinction between stress
_as	accent sensitive	Distinguish between stress
_ci	case insensitive	Case insensitive
_cs	case sensitive	Case sensitive
_bin	binary	Compare in binary mode

The last column, Maxlen, represents the character set and indicates how many bytes a character needs at most.

Common operation 1:

Check GBK character set comparison rules

SHOW COLLATION LIKE 'gbk%'; # SHOW COLLATION LIKE 'utf8%';Copy the code

Common operation 2:

# SHOW VARIABLES LIKE '%_server'; # SHOW VARIABLES LIKE '%_database'; # SHOW CREATE DATABASE dbtest1; ALTER DATABASE DBtest1 DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTf8_general_CI ';Copy the code

Common operation 3:

Show create table employees; Show table status from atguigudb like 'employees'; ALTER TABLE EMP1 DEFAULT CHARACTER SET 'utF8' COLLATE 'UTf8_general_ci ';Copy the code

5.4 Character set changes during request to response

System variables	describe
character_set_client	The character set used by the server to decode the request
character_set_connection	The server processes the request string fromcharacter_set_clienttocharacter_set_connection
character_set_results	The character set used by the server to return data to the client

The default values for these system variables on my computer are as follows (default values may vary from operating system to operating system) :

To reflect changes in the character set during request processing, we purposely change the value of a system variable:

mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)
Copy the code

Now suppose our client sends a request with the following string:

SELECT * FROM t WHERE s = 'I ';Copy the code

• To help you understand this process, we will analyze only the character ‘I’ character set conversion in this process. Now look at the change in character set between the request being sent and the result being returned:

  1. The character set used by the client to send the request

  Generally, the character set used by the client is the same as that used by the current OPERATING system. The character set used by different operating systems may vary as follows:

  • Unix-like systems use UTF8
  • Windows uses GBK

  When the client uses the UTF8 character set, the character ‘I’ in bytes sent to the server is: 0xE68891

  Note that if you are using visual tools such as Navicat, these tools will probably use a custom character set to encode strings sent to the server rather than the OS default character set (so try to use the command line window when learning).

  2. The server receives a binary string of bytes from the client, considers the character set of the byte to be character_set_client, and converts the byte to the character_SET_CONNECTION character set.

  Because the character_set_client value on my computer is UTF8, the byte 0xE68891 will be decoded according to the UTF8 character set, and the resulting character string will be ‘I’, and then the character_set_Connection character set will be used. GBK is encoded, and the result is the byte 0xCED2.

  3. Because the table t column col USES is GBK character set, agree with character_set_connection, so directly to find a byte value of 0 in column xced2 record, finally found a record.

  Hint another character set conversion is required if the character set used by a column is inconsistent with the character set represented by character_set_connection.

  4. The COL column in the record found in the previous step is actually a byte string 0xCED2. The COL column is encoded in GBK, so this byte string will be decoded in GBK first to get the string ‘I’. Then encode the character string using the character_set_Results character set (UTF8) to get a new byte: 0xE68891, and send it to the client.
  5. Because the client uses the UTF8 character set, 0xE68891 can be interpreted as character I and displayed on our monitor, so that we humans can read the result.

  The summary diagram is as follows:

6.SQL case specification

6.1 Differences between Windows and Linux Platforms

In SQL, keywords and function names are case-insensitive, such as SELECT, WHERE, ORDER, GROUP BY, and ABS, MOD, ROUND, and MAX.

However, in SQL, you still need to determine the case specification, because you may encounter different case issues in Linux and Windows environments. Windows systems are case insensitive by default, but Linux systems are case sensitive. Run the following command to view the information:

SHOW VARIABLES LIKE '%lower_case_table_names%'
Copy the code

In Windows:

In Linux:

lower_case_table_namesParameter value Settings:

• The default value is 0 and case sensitive.
• Set to 1, case insensitive. The created tables and databases are stored in lowercase on the disk. SQL statements are converted to lowercase to search for tables and databases.
• Set 2, create tables and databases according to the statement format, all lookups are converted to lowercase.

SQL case difference between the two platforms

MySQL is case-sensitive for database names, table names, table aliases, and variable names. 2. Keywords and function names are case-insensitive in SQL; 3. Column names (or field names) and column aliases (or field aliases) are case insensitive in all cases; MySQL is all case insensitive on Windows

6.2 Setting Case Rules in Linux

To make it case-insensitive, add lower_case_table_names=1 to the my.cnf configuration file [mysqld] and restart the server.

• However, the original database and tables need to be converted to lowercase before restarting the database instance, otherwise the database name will not be found.
• This parameter applies to MySQL5.7. Under MySQL 8, disallow the MySQL service when restartinglower_case_table_namesSet to something different than when the MySQL service was initializedlower_case_table_namesValue. If you must make MySQL8 case-insensitive, do the following:

3. Add lower_case_table_names=1 to the MySQL configuration file (/etc/my.cnf). 4

6.3 SUGGESTIONS for COMPILING SQL

If you don’t have a consistent naming convention for variable names, errors can occur. Here’s a suggestion for naming conventions:

Keywords and function names are all capitalized; Database name, table name, table alias, field name, field alias, and so on all lowercase; SQL statements must end with a semicolon.

Database names, table names, and field names are case sensitive in Linux MySQL, so it is recommended that you use the same naming rules for these fields, such as all lowercase fields.

Although keywords and function names are case insensitive in SQL, they can be executed if they are lowercase. However, both keyword and function names are capitalized to distinguish between database names, table names, and field names.

7. Set the SQL_mode properly

7.1 Loose mode vs. Strict Mode

Loose mode: If set to loose mode, when we insert data, even if given a wrong data, may be accepted, and no error is reported.

For example: Char (10) when I create a table with name as the column type, if I insert a table with name as the column type exceeding 10, for example, ‘1234567890abc’ exceeding 10, if I insert a table with name as the column type exceeding 10, for example, ‘1234567890abc’ exceeding 10, The first 10 characters are saved as’ 1234567890 ‘and’ ABC ‘is not. However, the data we gave was wrong because it exceeded the field length, but no error was reported, and mysql took care of it and accepted it. This is the effect of loose mode.

Application scenario: The SQL mode is set to loose mode to ensure that most SQL statements comply with the standard SQL syntax. In this way, when applications are migrated between databases, service SQL does not need to be greatly modified.

Strict mode: the above loose mode error should be reported, so MySQL5.7 has changed the default sql_mode to strict mode. Therefore, in production and other environments, we must adopt strict mode, and then the database of the development and test environment must be set up, so that problems can be found during the development and test phase. And even if we’re using MySQL5.6, we should change it to strict mode ourselves.

Development Experience: MySQL database such as total want to themselves down all operations on data, including data check, actually development, we should be in the development of its project application level will be the check to do, although the writing project in trouble a few steps, but after doing so, we in the database migration or move in the project, It would be a lot easier.

Problems that may occur after changing to strict mode:

If NO_ZERO_DATE is included in the setting mode, the MySQL database will not allow the insertion of a zero date, and the insertion of a zero date will raise an error rather than a warning. For example, a table containing a field TIMESTAMP column (if it is not declared NULL or a DEFAULT clause is displayed) will be automatically assigned DEFAULT ‘0000-00-00 00:00:00’ (zero TIMESTAMP), which obviously does not satisfy NO_ZERO_DATE in SQL_mode.

7.2 Loose Mode for example

Loose mode Example 1:

select * from employees group by department_id limit 10;

set sql_mode = ONLY_FULL_GROUP_BY;

select * from employees group by department_id limit 10;
Copy the code

Loose mode Example 2:

Set sql_mode to STRICT_TRANS_TABLES and insert data:

7.3 Viewing and Setting Mode

View the current SQL_mode

Select @@session.sql_mode from @@global.sql_mode select @@global.sql_mode from @@global.sql_modeCopy the code

Temporary setting: Set sql_mode in the current window

SET GLOBAL sql_mode = 'modes... '; SET SESSION SQL_mode = 'modes... '; # current sessionCopy the code

For example:

# Change to strict mode. This method only takes effect in the current session. Closing the current session does not take effect. set SESSION sql_mode='STRICT_TRANS_TABLES'; # Change to strict mode. This method takes effect in the current service and becomes invalid after the MySQL service is restarted. set GLOBAL sql_mode='STRICT_TRANS_TABLES';Copy the code

/etc/my. CNF sql_mode = /etc/my. CNF /my. ini

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR
_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Copy the code

Then restart MySQL.

Of course, the production environment is forbidden to restart the MySQL service, so use the temporary setting mode and permanent setting mode to solve the online problem. Even if the MySQL service is restarted one day, it will take effect permanently.

Refer to the article

Chapter 3 “MySQL Technology Insider: InnoDB Storage Engine (version 2)” “Database index design and Optimization”