Detailed explanation of the chronic diseases of MySql

1. Default encoding set problem

When displaying front-end data, all select * from t_news can be found All content. Then it won’t work when parameterizing queries. For example:

Select * from t_news where title like ‘%Source Code Education%’; 0 pieces of data were found.

Select * from t_news where id = 1. 1 piece of data was found.

Pay attention to the fields marked in red. This is after I modified it. They were originally latin1.

Solution:

The configuration file of MySQL under Windows is my.ini, which is usually in the root directory of the installation directory.

The configuration file of MySQL under Linux is my.cnf, which is generally placed in /etc/my.cnf, /etc/mysql/my.cnf. If you can't find it, you can use the find command to find it.

Open my.ini/my.cnf and modify the following two fields.

Ps: You need to restart mysql after modifying the configuration file.

2. Data is not rolled back

During programming, spring configures transactions, but when an exception occurs, the data in the database still changes. After confirming that there are no problems with the transaction and code configuration, we consider the possibility of specializing the database. We use the "Navicat" tool to design the table. In the "Options" column we find that the storage engine used by the table is MyISAM.

MyISAM does not support transactions. Rollback will cause incomplete rollback and is not atomic.

Change MyISAM to InnoDB.

There is also a default storage engine in the MySQL configuration file (my.ini). If there is no special statement when creating the table, the default storage engine will be used.

3. Optimize the import of large amounts of data

In our work, we will inevitably encounter the situation of importing large amounts of data. For example, you need to query previously backed up data, import real data locally for testing, regularly import data into a backup database, and other business scenarios.

MySQL provides the import method of load data infile. However, improper configuration will also lead to slow import speed.

Solution:

Increase the following parameter value, usually 80% of the physical memory. But 32-bit systems will be limited to 2-3.5G.

4. Enable custom function function (cloud database)

Sometimes when we run other people’s code, or when transplanting the server, we may encounter the database previously defined Function cannot be used. Especially with databases, the function function is not enabled by default, resulting in the function not being executed.

First we check whether the function is enabled. As shown below:

The custom function function here is turned off by default.

Solution: Use "set global log_bin_trust_function_creators = 1;" to enable custom function functionality.

Query again:

But this method is only a zero-time solution. After MySQL is restarted, the function will be turned off by default.

To permanently enable the custom function function, you need to add "log-bin-trust-function-creators=1" to the configuration file.

5. Table name case issues in Linux environment

In Linux system environment, MySQL table names are case-sensitive by default. Sometimes if a capital letter is entered incorrectly in the table name, the query cannot be found.

Solution: Add "lower_case_table_names=1" at the end of the my.cnf configuration file. Then restart MySQL.

"lower_case_table_names=1 "This sentence means to force the input statement to lowercase and then execute the statement.