According to the information,
group by has been optimized after MySQL 5.7. The version after the default startup improvement enables ONLY_FULL_GROUP_BY mode.
That is, ONLY_FULL_GROUP_BY is a sql_mode provided by the MySQL database, and this sql_mode is used to ensure the validity of the SQL statement “grouping for the most value”. This mode adopts the processing method with databases such as Oracle and DB2. That is, columns with ambiguous semantics are not allowed in the select target list.
As long as there are aggregation functions sum(), count(), max(), avg() and other functions, group by needs to be used
mysql reports Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Cause: An error is reported due to the mysql8.0 encryption method.
mysql -u root -p
alter user ‘root’@’localhost’ identified with mysql_native_password by ‘123456’;
Note: 123456 is my own password to connect to the database
Mysql has been unsuccessfully started, check the err file under data, and find that
The solution is as follows:
1. Shut down the mysqld process
2. Delete the two files in the data directory
MySQL Error: Failed to start mysql.service: Unit mysql.service is masked.
➜ /home/penelope systemctl unmask mysql.service
The cause of the error has not been found out yet
Every derivedtable must have its own alias
Each derived table needs to have an alias
Change the MySQL statement to: select count (*) from (select * from…) as total
After installing MySQL today, an error occurred when opening it:
1. Open the corresponding folder to view the error message:
2. Open the error information file and check that the error reason is: plug in ‘federated’ is disabled./usr/SBIN/mysqld: table ‘mysql. Plugin’ doesn’t exist
3. The reason why ‘mysql. Plugin’ does not exist is that after the newly installed MySQL service, it is generally necessary to perform database initialization operation to generate permission related tables. The execution command is as follows:
4. If the command is executed successfully, it is best. If the following error is reported:
The solution is to install the Autoconf library
yum -y install autoconf
5. Then execute the database open command, it will succeed
service mysql start
Tip: it’s better to install the database in usr/local/because many things by default point to this folder