Problems encountered in using MySQL

Here are some problems encountered in the use of MySQL and their corresponding solutions.

sql_mode=only_full_group_by causes group by query error

question
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 MySQL
sudo mysql -hlocalhost -uroot -p123123
SET 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
# Add the following sentence at the bottom sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# Then restart sudo service mysql restart

Detailed explanation
The default value of sql_mode is null. Under this setting, some illegal operations can be allowed, such as allowing the insertion of some 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 discovered during the development and testing 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 this SQL is illegal because the column is not in the GROUP BY clause.
NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of auto-increment columns. By default, inserting 0 or NULL will generate the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.
STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and no restrictions are imposed on non-transaction tables.
NO_ZERO_IN_DATE: In strict mode, do not allow day and month to be zero
NO_ZERO_DATE: Set this value, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning
ERROR_FOR_DIVISION_BY_ZERO: During an INSERT or UPDATE, if data is divided by zero, an error is generated instead of a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.
NO_AUTO_CREATE_USER: Disable GRANT from creating users with empty passwords
NO_ENGINE_SUBSTITUTION: Throws an error if the required storage engine is disabled or not compiled in. If this value is not set, the default storage engine is used instead and an exception is thrown.
PIPES_AS_CONCAT: Treat "||" as a string concatenation operator instead of an OR operator, which is the same as Oracle database and similar to the string concatenation function Concat
ANSI_QUOTES: When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings because they are interpreted as identifiers.

Clear table data

Problem: When performing data migration or data repair, it is often necessary to clear or 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 it. For tables with constraints, this will not work. You must first disable the foreign key constraints and then perform the deletion.
Another method is to delete all the data in the table and then set the starting value of the table id to 1.

# The first solutionSELECT @@FOREIGN_KEY_CHECKS; # View the single-signature foreign key constraint commandSET FOREIGN_KEY_CHECKS=0; # Disable the foreign key constraint firstTRUNCATE TABLE table_name; # Then reset the table# The second solution is recommendedDELETE FROM table_name; # Delete command without a where condition to delete all dataALTER TABLE table_name AUTO_INCREMENT=1; # Reset the initial value of the increment

MySQL takes up too much memory

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

By optimizing the configuration of MySQL, the purpose of reducing its memory usage can be achieved. 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 detailed configuration items. The server I am using now has a single-core CPU and 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 = 600
# 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, the default is 2000 
table_open_cache = 128 
# The cache size of the innodb engine. If startup fails, reduce innodb_buffer_pool_size = 600M 
# The buffer size that can be used for joint query operations, thread exclusive join_buffer_size = 8M

After modifying the configuration, restart the MySQL service to make the configuration items take effect.

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

# Log in to mysql
mysql -hlocalhost -uroot -ppassword
# View all global configurations show global variables;
# View the global configuration status show global status;
# Filter specific configuration items. The following is to filter out the configuration items starting with innodb show global variables like 'innodb%';

MySQL temporary tables must have aliases

For temporary tables generated by queries, you must use as to define an alias, even if it is not used later. Otherwise an error will be reported: Every derived table must have its own alias.

MySQL Get table column names

Assume that the table name is table_name; you can use the following three commands to query the column names of the table.

DESC table_name;

DESCRIBE table_name;

SHOW columns FROM persons;