Category:MySql

  • MySQL concurrent processing

    Time:2021-10-27

    Concurrent processing When multiple connections modify records, ensure data consistency and integrity Why use concurrency control Suppose there are commodity records in our database, and now there are two users logging in. Suppose a user wants to delete a record with ID 22 and a user wants to read a record with ID 22, what […]

  • Introduction to MySQL Source command

    Time:2021-10-27

    Reflections on an online problem At work today, my development colleagues took a. Zip compressed package file and said that they wanted to pour the data into the database. Originally, they thought it was formed SQL. They just need to copy and paste it and pour it into the database. When I got it, I […]

  • MySQL FAQs

    Time:2021-10-26

    reset password Close MySQL servicesudo /etc/init.d/mysql stopRun in MySQL safe mode and skip permission verificationsudo mysqld_safe –user=mysql –skip-grant-tables –skip-networking &Log in to MySQL as root with an empty passwordsudo mysql -u root mysqlUpdate passwordupdate user set Password = PASSWORD(‘root’) where User =’root’;update mysql.user set authentication_string=password(‘root’) where user=’root’ ;Refresh permissionsFLUSH PRIVILEGES;sign outquitThe password is too simple. […]

  • Addition, deletion, modification and query of MySQL common command manual

    Time:2021-10-26

    1、 Database operation Create database Syntax: CREATE DATABASE database_name; Delete database Be careful when deleting the database! Because after executing the delete command, all data will disappear. Syntax: DROP DATABASE database_name; Select database Syntax: USE database_name; 2、 Data table operation data type value type type size purpose TINYINT 1 byte Small integer value SMALLINT 2 […]

  • Optimized implementation of count() for MySQL large tables

    Time:2021-10-26

    The following is my judgment based on the combination of the data structure of the B + tree and the speculation of the experimental results. If there is any error, please correct it! Today, I experimented with the count () operation optimization of MySQL. The following discussion is based on mysql5.7 InnoDB storage engine. X86 […]

  • MySQL restores data through binlog

    Time:2021-10-25

    objective By understanding the relevant configuration of binlog log, you can simply master the data recovery operation of database through binlog; MySQL log file Any mature software will have a mature log system. When there is a problem with the software, these logs are the treasure house to query the source of the problem. Similarly, […]

  • Introduction to database transactions

    Time:2021-10-25

    1、 Transaction classification Transaction is a program execution unit that accesses and updates various data items in the database. Transaction will convert the database from one consistent state to another consistent state. This is the purpose of transaction and one of the important characteristics that distinguish the transaction model from the file system. Transactions in […]

  • Implementation method of adding field annotation to MySQL create table

    Time:2021-10-25

    Direct Po codes and cases #Write comments when creating tables CREATE TABLE userinfo( Id int comment ‘number’, Uname varchar (40) comment ‘user name’, Address varchar (120) comment ‘home address’, Hobby varchar (200) comment ‘hobby’ )Comment = ‘user information table’; #Modify comments for tables Alter table userinfo comment ‘user information table’; #Modify the comments of the […]

  • Indexing principle and slow query optimization

    Time:2021-10-24

    introduce This blog will introduce the following knowledge: Index introduction Indexing principle Data structure of index (binary tree — > balanced binary tree — > b tree — > b + tree) Clustered index and secondary index MySQL index management Syntax for creating and deleting indexes Test after index creation (change in query speed) How […]

  • Method for cleaning data in MySQL online database

    Time:2021-10-24

    01 scenario analysis This afternoon, the development colleagues asked to delete some data records online. After a brief look at the data distribution, it is about to delete more than 27 million records in the data table, and the total records in the data table are more than 28 million, that is, the records to […]

  • Leetcode MySQL exercise one – employees who exceed the manager’s income

    Time:2021-10-23

    The employee table contains all employees, and their managers also belong to employees. Each employee has an ID and a list of the ID of the manager corresponding to the employee. Id Name Salary ManagerId 1 Joe 70000 3 2 Henry 80000 4 3 Sam 60000 NULL 4 Max 90000 NULL The Department table contains […]

  • Detailed explanation of database transaction

    Time:2021-10-23

    1、 Business 1. What is a transaction A transaction consists of one or moreSQLThe logical execution unit composed of statements can be compared to a container, which contains a pile ofSQLStatements, either all of which are executed successfully or none of which can be executed successfully(Atomicity) 2. Why use transactions When performing a series of […]