• Explain the correct posture of if function in MySQL


    In order to write today’s content, run the program for nearly seven hours, stored 10 million pieces of data in the database. —— What I want to talk about today is an example of if() function of MySQL database. The specific scenarios are as follows, Let’s look at the table structure CREATE TABLE `message` ( […]

  • MySQL 8.0.18 hash join does not support left / right join connection


    In MySQL 8.0.18, a new function of hash join has been added, which is applicable to the fields that have not been indexed to do equivalent Association query. In previous versions, if the joined fields were not indexed, the query speed would be very slow, and the optimizer would use BNL (block nesting) algorithm. Hash […]

  • Usage analysis of MySQL database show processlist instruction


    In the actual project development, if we have a lot of pressure on the database, such as a large number of SQL queries or inserts, especially in the case of multithreading, we can kill some of the slow SQL, and a common command is show processlist 1. What is show processlist Show processlist: by looking […]

  • Summary of MySQL lock knowledge


    The concept of lock ① Lock, in real life, is a tool that we want to hide from the outside world. ② In a computer, it is a mechanism to coordinate multiple processes or threads to access a resource concurrently. ③ In the database, in addition to the traditional computing resources (CPU, ram, I / […]

  • Infinite classification method class definition and application examples based on PHP + MySQL


    This paper describes the definition and use of infinite classification method class implemented by PHP + mysql. For your reference, the details are as follows: Create databases and tables CREATE DATABASE `sortclass`DEFAULT CHARSET utf8; CREATE TABLE IF NOT EXISTS `class` ( `cid` mediumint(8) unsigned NOT NULL auto_increment, `pid` mediumint(8) unsigned NOT NULL, `cname` varchar(50) NOT […]

  • The simplest way to give new users and permissions by MySQL


    Create a user: create user ‘oukele’@’%’ identified by ‘oukele’;   If you are prompted with the information listed below, you need to refresh the permission table The MySQL server is running with the –skip-grant-tables option so it cannot execute this st… The steps are as follows: Extended learning MySQL creates a new user and grants […]

  • Master slave replication knowledge points arrangement of MySQL


    When a single MySQL server can not meet the current website traffic optimization scheme. MySQL Cluster technology needs to be built. 1、 Function: When data is inserted, modified and deleted into the master server, the data is automatically synchronized to the slave server. Note: master slave replication is unidirectional and can only be master to […]

  • MySQL realizes page turning query function


    First of all, we should make clear why we should use paging query. Because of the huge data, it is impossible for all queries to be displayed on the page. If all of them are displayed on the page, the query speed will be slow. Therefore, paging query solves the problems of ① data query; […]

  • Practice of MySQL 100 million level database


    Demand background Horizontal split and vertical split have been the most common database optimization methods. The database used by the author’s Department has always been a master-slave hot standby architecture, but the amount of data has exceeded 100 million a year ago, and has been increasing at a rapid growth rate. In order to reduce […]

  • Installation tutorial of MySQL 8. X under Windows


    MySQL 5.7 has been used before, but due to some new features added to MySQL, I chose to update it. Download MySQL Enter the download address of MySQL official website, select windows (x86, 64 bit), zip archive. Download address: https://dev.mysql.com/downloads/mysql/ You can skip it without logging in. The download process may be slow. Wait patiently. […]

  • ERROR 1862 (HY000): Your password has expired. To log in you must change it using a …..


    Error message: ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords Causes of problems: It may be that during the installation of MySQL, after you initialize the MySQL operation through mysqld — initialize, and generate a temporary password, you do not directly connect […]

  • ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’


    Error message: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) Causes of problems: It could be my.cnf The parameter socket of [mysqld] is set in the configuration file, but the parameter socket of [Client] is not set mysql.sock What is the purpose of the document MySQL supports socket and TCP […]