MySQL advanced summary

MySQL UPDATE multi table Association update 1.MySQL can UPDATE data based on multi table query. For the UPDATE operation of multiple tables, you need to be careful. It is recommended to use the SELECT statement to query and verify whether the updated data is consistent with your expectations before updating. Build two tables, one for prod ...

Added by fisel on Mon, 28 Feb 2022 08:11:11 +0200

15 must know Mysql index failure scenarios, stop stepping on the pit!

backgroundWhether you're a technology guru or a newcomer to Xiaobai, you will step into the pit of Mysql database without index from time to time. A common phenomenon is that the index is clearly added to the field, but it does not take effect.A few days ago, I encountered a slightly special scenario. The same SQL statement takes effect under s ...

Added by Edd on Mon, 28 Feb 2022 03:23:16 +0200

MySQL uses binlog logs to solve the problem of deleting databases and tables

First, create a simulation environment. mysql> create database testdb; mysql> use testdb; mysql> CREATE TABLE `people` ( `name` varchar(20) DEFAULT NULL, `age` varchar(20) DEFAULT NULL, `birth` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; mysql> CREATE TABLE `pet` ( `name` varchar(20 ...

Added by bonkaz on Sun, 27 Feb 2022 09:00:39 +0200

Gap Lock in MySQL InnoDB

Lock List Shared and Column Exclusive LocksIntent LockRecord LockGap lockNext-Key LockInsert Intent LockAUTO-INC lock This time we will only discuss and experiment with intent locks. Gap lock Gap locks are also index records of locks, locking values that do not exist in gaps. What is gap lock? Gap locks are locks on gaps between index rec ...

Added by ksukat on Sat, 26 Feb 2022 20:12:27 +0200

Manage hierarchical data in MySQL - adjacency table model and nested set model

backgroundIn our daily development, one kind of data we will come into contact with is hierarchical data. What is tiered data? Business organization chart, content management category, RBAC permission management, product category, etc. These are hierarchical data. The following is the product category hierarchy of an e-store:In this paper, we w ...

Added by bad_gui on Sat, 26 Feb 2022 17:36:42 +0200

Use of Java - jdbc

Chapter 1 JDBC overview Before that, we learned JavaSE and wrote Java programs. The data is stored in variables, arrays, sets, etc., which cannot be persisted. Later, we learned that IO streams can write data to files, but it is not convenient to manage data and maintain the relationship between data; Later, we learned the database manage ...

Added by dcro2 on Sat, 26 Feb 2022 16:47:26 +0200

I'm learning database function at station b

Previous: I'm learning database at station b (8): multi table operation exercise Function classification Aggregate function, mathematical function, string function, date function, control flow function, window function 1, Aggregate function 1. Introduction Aggregate functions are mainly composed of count, sum, min, Max and AVG. these aggregat ...

Added by dpronk on Sat, 26 Feb 2022 14:11:32 +0200

Why do I suggest adding force index to all queries on complex but performance critical tables

Recently, I encountered slow SQL again. After a brief look, it is also because MySQL optimizer has the problem of inaccurate estimation of query plan. The SQL is as follows:select * from t_pay_record WHERE (( user_id = 'user_id1' AND is_del = 0 )) ORDER BY id DESC LIMIT 20The SQL took 20 minutes to execute before there was a ...

Added by plisken on Sat, 26 Feb 2022 14:03:10 +0200

Research on Innodb lock mechanism - gap lock

Lock algorithm in Innodb There are generally three locking algorithms commonly used in innodb, namely 1. Record lock 2. Gap Lock 3. next key lock The Next key lock is the combination of Gap lock and Record lock. It locks a range and locks the Record itself. In other words, the next key lock is not a separate lock. As far as I understand, ...

Added by Black Hobbit on Fri, 25 Feb 2022 10:59:43 +0200

News crawling release management, submission and chat front and back management system based on SpringBoot+Java+MySQL

introduce This topic follows the theory of software engineering, as well as the software life cycle of requirements analysis, system design, coding and testing. The system is developed using the SpringBoot framework, which provides a full-featured MVC module for building Web applications. It is mainly composed of dispatcher servlet, handler ma ...

Added by greensweater on Fri, 25 Feb 2022 06:45:22 +0200