Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the common problems in MySql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. Default code set problem

When doing the foreground data display, query all select * from t_news to find all the content. Then not when parameterizing the query. 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 red field, this is after I modified, it turned out to be latin1.

Solution:

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

The configuration file of MySQL under Linux is my.cnf, which is usually 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 to modify the following two fields.

Ps: restart mysql after modifying the configuration file.

2. Data is not rolled back

When programming, spring configures transactions, but the data in the database still changes when an exception occurs. In the case of confirming that there is no problem with the transaction and code configuration, we put the possibility special database. We used the "Navicat" tool to design the table, and in the options column, we found that the storage engine used for the table was MyISAM.

MyISAM does not support transactions, rollback will result in incomplete rollback, not atomicity.

Change MyISAM to InnoDB.

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

3. Optimize the import of a large amount of data

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

MySQL provides a way to import load data infile. However, improper configuration can also lead to slow import.

Solution:

Increase the latter parameter value, which is generally 80% of the physical memory. But 32-bit systems will be limited to 2-3.5G.

4. Enable custom function (cloud database)

Sometimes when we run other people's code, or when we migrate the server, we may encounter a situation where the functions previously defined by the database cannot be used. Especially with the database, the default is not to turn on the function function, resulting in the function does not execute.

First of all, let's see if the function function is enabled. As shown below:

Here the custom function function is off by default.

Solution: turn on the custom function using "set global log_bin_trust_function_creators = 1;".

Query again:

But this is only an 00:00 solution, and after MySQL restarts, the feature 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 problem in linux environment

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

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

The sentence "lower_case_table_names=1" means to cast the input statement to lowercase and then execute the statement.

This is the end of this article on "what are the common problems in MySql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.