Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the features that MySQL does not support. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Features not supported by MySQL

Features found in other databases but not in MySQL are described here. It describes what features are omitted and what to do if they are needed. In general, MySQL ignores certain features because of their negative performance impact. Some functions are on the developer's to-do list, once a way is found to achieve the corresponding functions without affecting them.

Goals with good performance will be achieved.

■ sub-selection. A sub-selection is an SELECT statement nested within another SELECT statement, as shown in the following query:

SELECT * FROM score

WHERE event_id IN (SELECT event_id FROM event WHERE type = "T")

The sub-selections are intended to be given in MySQL3.24, and then they will not be ignored. But by then, many queries written with sub-choices can also be written with joins. Please refer to 3. 8. Section 1, "write sub-selections as connections".

■ transaction processing and commit / fallback. A transaction is a set of SQL statements executed by other clients as a whole without interruption. The commit / fallback function allows you to specify that several statements are executed as a whole or not. That is, if any of the statements in the transaction fail, all statements executed before the statement are undone.

YSQL automatically synchronizes a single SQL statement so that clients don't interfere with each other. For example, two clients cannot write the same table at the same time. In addition, you can use LOCK TABLES and UNLOCK TABLES to combine several statements as a whole, which enables you to perform operations that cannot be met by concurrency control of a single statement. The problem with MySQL with transactions is that it cannot automatically organize several statements, and if any of these statements fail, they cannot be rolled back.

In order to understand why transaction processing is useful, examples can be given. If you work in the clothing sales industry, update the inventory whenever your salesperson makes a sale. The following example illustrates the problems that can occur when multiple salespeople update the database at the same time (if the initial number of shirts in stock is 4 7):

T1 salesman 1 sold 3 shirts

T2 salesperson retrieves the current shirt count (4 7):

SELECT quantity FROM inventory WHERE item = "shirt"

T3 salesman 2 sold 2 shirts

T4 salesperson 2 retrieves current shirt count (4 7)

SELECT quantity FROM inventory WHERE item = "shirt"

T5 salesman 1 calculates the new number of inventory as 47-3 = 44 and sets the shirt count to 44:

UPDATE inventory SET quantity = 44 WHERE item = "shirt"

T6 salesman 2 calculates the new number of inventory as 47-2 = 45 and sets the shirt count to 45:

UPDATE inventory SET quantity = 45 WHERE item = "shirt"

By the end of this sequence of events, you have sold five shirts, but the number of shirts in stock is 45 instead of 42. The problem is that if you view the inventory in one statement and update its value in another, this is a multi-statement transaction. The activity performed in the second statement depends on the value retrieved in the first statement. However, if independent transactions occur within an overlapping time range, the statements of each transaction will be entangled and interfere with each other. In a transactional database, the statement of each salesperson can be executed as a transaction, so that the statement of salesperson 2 will not be executed until the statement of salesperson 1 is completed. In MySQL, you can do this in two ways:

■ method 1: execute a set of statements as a whole. You can use LOCK TABLES and UNLOCK TABLES to organize statements together and execute them as an atomic unit: lock the tables you want, publish the query, and then release the locks. This prevents others from using these tables when you lock them. With table synchronization, the inventory situation is as follows:

T1 salesman 1 sold 3 shirts

T2 salesperson 1 requests a lock and retrieves the current shirt count (47)

LOCK TABLES inventory WRITE

SELECT quantity FROM inventory WHERE item = "shirt"

T3 salesman 2 sold 2 shirts

After reading the above, do you have any further understanding of the features that MySQL does not support? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.