Record of problems encountered in the use of MySQL

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

sql_mode=only_full_group_by Causes group by Query Error

Problem
MySQL Execute GROUP BY Query Error Report:

#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  Can be solved 
#  Landing 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 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, 1 illegal operation can be allowed, such as 1 illegal data insertion. This value must be set to strict mode in production environment, so the database of development and test environment must also be set, so that problems can be found in development and test stage. The common values for sql_mode are as follows:

ONLY_FULL_GROUP_BY: For an GROUP BY aggregation operation, 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 generating the next 1 self-growing values. 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 1 value cannot be inserted into 1 transactional table, the current operation is interrupted without restrictions on non-transactional tables
NO_ZERO_IN_DATE: In strict mode, zero dates and months are not allowed
NO_ZERO_DATE: Set this value, the mysql database does not allow inserting a zero date, which throws an error instead of a warning
ERROR_FOR_DIVISION_BY_ZERO: If data is divided by zero during INSERT or UPDATE, an error is generated instead of a warning. If this 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: An error is thrown if the required storage engine is disabled or not compiled. If this value is not set, replace it with the default storage engine and throw 1 exception
PIPES_AS_CONCAT: Treat "" as a string concatenation operator rather than an OR operator, which is identical to the Oracle database and similar to the string splicing function Concat
ANSI_QUOTES: With ANSI_QUOTES enabled, a string cannot be referenced in double quotation marks because it is interpreted as an identifier

Empty table data

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

Solutions

You can reset the data table using the command TRUNCATE TABLE table_name, which essentially deletes the table and then rebuilds it. It is not feasible for constrained tables. You must disable the foreign key constraint before performing the deletion.
Another method is to delete all the data in the table, and then set the id starting value of the table to 1.

#  No. 1 1 Species scheme 
SELECT @@FOREIGN_KEY_CHECKS;  #  View single-sign foreign key constraint command 
SET FOREIGN_KEY_CHECKS=0;  #  Disable foreign key constraints first 
TRUNCATE TABLE table_name;  #  Then reset the table 
 
#  No. 1 2 Kinds of schemes, recommended 
DELETE FROM table_name;  #  Without where The conditional delete command deletes all data 
ALTER TABLE table_name AUTO_INCREMENT=1; #  Reset increment initial value 

MySQL uses 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 will easily occupy 400M or even 800M memory.

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

# The maximum number of instrumented table objects. 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, which defaults to 2000 
table_open_cache = 128 
# innodb The cache size of the engine, which is reduced if the startup fails 
innodb_buffer_pool_size = 600M 
#  Buffer size that can be used by union query operation, which is exclusive to threads  
join_buffer_size = 8M

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

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

#  Landing mysql
mysql -hlocalhost -uroot -ppassword
#  View all global configurations 
show global variables;
#  View global configuration transitions 
show global status;
#  Filter specific configuration items, and the following are filtered out innodb Beginning configuration item 
show global variables like 'innodb%';

MySQL Temporary tables 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 Get the column name of the table

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;