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

This article is about how to solve the problems encountered in the use of MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Sql_mode=only_full_group_by causes group by query to report error

problem

MySQL executes GROUP BY query and reports an error:

# 1-Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

Solution.

# set the correct sql_mode to solve the problem # Log in to MySQLsudo mysql-hlocalhost-uroot-p123123SET GLOBAL sql_mode= (SELECT REPLACE (@ @ sql_mode,'ONLY_FULL_GROUP_BY',''))

You can also modify the MySQL configuration file and restart the MySQL service

# Open the MySQL configuration file sudo vim / etc/mysql/conf.d/mysql.cnf# and add the following sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION# at the bottom and restart sudo service mysql restart

Detailed explanation

The default value of sql_mode is null, which allows some illegal operations, such as the insertion of illegal data. This value must be set to strict mode in the production environment, so the database of the development and test environment must also be set so that problems can be found during the development test phase. Common values for sql_mode are as follows:

ONLY_FULL_GROUP_BY: for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause

NO_AUTO_VALUE_ON_ZERO: this value affects the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. This option is useful if the user wants to insert a value of 0 and the column is self-growing.

STRICT_TRANS_TABLES: in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and there are no restrictions on non-transaction tables

NO_ZERO_IN_DATE: date and month are not allowed to be zero in strict mode

NO_ZERO_DATE: set this value. Zero date is not allowed to be inserted in mysql database. Inserting zero date will throw an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO: in the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero

NO_AUTO_CREATE_USER: prohibit GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION: throw an error if the required storage engine is disabled or uncompiled. When this value is not set, it is replaced with the default storage engine and an exception is thrown

PIPES_AS_CONCAT: treats "| |" as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function Concat

ANSI_QUOTES: when ANSI_QUOTES is enabled, you cannot refer to a string in double quotes because it is interpreted as an identifier

Clear the table data

problem

When performing data migration or data repair, it is often necessary to empty and reset the data table.

Solution

You can use the TRUNCATE TABLE table_name command to reset the data table, the essence of this command is to delete the table, and then rebuild the table, for constrained tables, it does not work, you must disable the foreign key constraint before performing the delete.

Another way is to delete all the data in the table, and then set the id starting value of the table to 1. 0.

# the first option SELECT @ @ FOREIGN_KEY_CHECKS; # View single signature foreign key constraint command SET FOREIGN_KEY_CHECKS=0; # first disable the foreign key constraint TRUNCATE TABLE table_name; # and then reset the table # the second option, DELETE FROM table_name; # delete command without where condition to delete all data ALTER TABLE table_name AUTO_INCREMENT=1; # reset increment initial value

MySQL takes up too much memory

When the server memory is only 1 gigabyte or less, you need to change the default configuration of MySQL, otherwise you will easily find that your MySQL takes up 400m or even 800m of memory.

By optimizing the configuration of MySQL to reduce its memory footprint, the configuration file is generally under [[mysqld]] in vim / etc/my.cnf. For detailed MySQL configuration items, please refer to the official website: MySQL official website. The server in front of my order is single-core CPU with 1G of memory.

# The maximum number of instrumented table objects. The maximum number of table instances loaded, default-1 adaptive performance_schema_max_table_instances = 60 # The number of table definitions that can be stored in the definition cache, default-1 adaptive table_definition_cache = 400 # The number of open tables for all threads. The maximum number of tables that can be opened by all threads. Default is 2000 table_open_cache = 128 # innodb engine cache size. If startup fails, reduce the buffer size that can be used by innodb_buffer_pool_size = 600m # joint query operation, thread exclusive join_buffer_size = 8m

After modifying the configuration, restarting the mysql service has enabled the configuration item to take effect.

You can view the current mysql configuration information in the following ways:

# Log in to mysqlmysql-hlocalhost-uroot-ppassword# to view all global configurations show global variables;# view global configuration transition show global status;# filter specific configuration items. Here is the configuration item show global variables like 'innodb%' that is filtered out at the beginning of innodb

MySQL temporary table must have an alias

For temporary tables generated by queries, aliases must be defined using as, even if they are not used later. Otherwise, an error will be reported: Every derived table must have its own alias.

MySQL gets the column name of the table

Assuming that the table name is table_name;, you can query the column names of the table using the following three commands.

DESC table_name;DESCRIBE table_name;SHOW columns FROM persons;, thank you for your reading! This is the end of this article on "how to solve the problems encountered in the use of 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, you can 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.