Features that MySQL does not support

Features that 3.9 MySQL does not support
This section describes features in other databases that are not available in MySQL. It describes what has been omitted and what to do when these features are needed. In general, MySQL ignores certain features because they have a negative performance impact. Some features are on the developer's to-do list, and once a method is found to achieve the appropriate functionality without affecting
The goal of good performance, they will be implemented.
Child selection. A child selection is a SELECT statement that is 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")
Sub-selections are intended to be given in MySQL3.24, and they will not be ignored by then. But then, many of the queries written with the child selection can also be written with a connection. Please refer to 3. 8. Section 1, "Writing a child selection as a connection."
Transaction processing and submission/fallback. A transaction is a set of SQL statements that are executed without interruption by other clients as a whole. The Commit/Rollback feature allows a number of statements to be executed or not executed as a whole. That is, if any of the statements in the transaction fails, the effect of all statements that are executed until the statement is undone.
Ysql automatically synchronizes a single SQL statement to avoid interference from the client. (for example, two clients cannot write to the same table at the same time.) In addition, you can use lock tables and unlock tables to make a whole number of statements, which enables you to complete actions that are not satisfied by the concurrency control of a single statement. MySQL's problem with transactions is that it does not automatically organize several statements and cannot be rolled back if one of those statements fails.
To figure out why a transaction is useful, you can illustrate it. If you work in the apparel sales industry, update the inventory number whenever your sales staff make a sale. The following example illustrates a problem that may occur when multiple salespeople update the database at the same time (if the initial inventory of shirts is 4 7):
T1 Sales 1 Sell 3 Shirts
T2 Sales staff retrieves current shirt count (4 7):
SELECT Quantity from inventory WHERE item = "Shirt"
T3 Sales 2 sell 2 Shirts
T4 Sales 2 Retrieve current shirt count (4 7)
SELECT Quantity from inventory WHERE item = "Shirt"
T5 Salesperson 1 calculates the new number of inventory for 47-3 = 44 and sets the shirt count to 44:
UPDATE Inventory SET quantity = The WHERE item = "Shirt"
T6 Salesperson 2 Calculates the new number of inventory for 47-2 = 45 and sets the shirt count to 45:
UPDATE inventory SET quantity = \ WHERE item = "Shirt"
At the end of this sequence of events, you have sold 5 shirts, but the inventory is 45 instead of 4 2. The problem is that if you view inventory in one statement and update its value in another statement, this is a multiple-statement transaction. The activity that is performed in the second statement depends on the value retrieved in the first statement. However, if independent transactions occur within overlapping time ranges, the statements for each transaction are entangled and interfere with each other. In a transaction-type database, statements for each salesperson can be executed as a transaction, so that the statement of Salesperson 2 is not executed until the statement of Salesperson 1 is complete. In MySQL, you can do this in two ways:
Method 1: Executes 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: The table to be used to lock the home, publish the query, and then release the locks. This prevents others from using them when you lock the tables. With table synchronization, the inventory is as follows:
T1 Sales 1 Sell 3 Shirts
T2 Salesperson 1 Request a lock and retrieve the current shirt count (47)
LOCK TABLES Inventory WRITE
SELECT Quantity from inventory WHERE item = "Shirt"
T3 Sales 2 sell 2 Shirts
T4 Sales 2 tried to get a lock: This was blocked because the salesperson 1 had taken up the lock:
LOCK TABLES Inventory WRITE
T5 Salesperson 1 calculates the new number of inventory for 47-3 = 44 and sets the shirt count to 44 and then releases the lock:
UPDATE Inventory SET quantity = The WHERE item = "Shirt"
UNLOCK TABLES
T6 now 2 of the sales person's lock request succeeded. Salesperson 2 Retrieve current shirt count (44)
SELECT Quantity from inventory WHERE item = "Shirt"
T7 Salesperson 2 Calculates the new number of inventory for 44-2 = 42, sets the shirt count to 4 2, and then releases the lock:
UPDATE Inventory SET quantity = The WHERE item = "Shirt"
UNLOCK TABLES
Now the statements from two transactions are not confused, and the number of shirts in stock is set correctly. We use a write lock here because we need to modify the inventory table. If you are simply reading a table, you can use the read lock. This lock allows other clients to read the table when you are using the table. In the example just cited, Salesperson 2 probably won't notice the difference in execution speed, because transactions are short and fast. However, as a general rule, it is important to avoid locking the table for a long time.
If you are using more than one table, you must lock them before you perform a group query. If you are only reading data from a particular table, you simply add a read lock to the table instead of a write lock. If you have a set of queries that want to make some changes to the inventory table, you need to read some data from the Customer table. In this case, a write lock is required on the inventory table, and a read lock is required on the Customer table:
LOCK TABLES Inventory Write,customer READ
...
UNLOCK TABLES
This requires you to lock and unlock the table yourself. The database system that supports transaction processing will do this automatically. However, in terms of grouping statements that are executed as a whole, they are the same regardless of whether they are in a database that supports transactional processing.
Method 2: Use relative update instead of absolute update. To resolve statement obfuscation problems from multiple transactions, you should eliminate the dependencies between statements. Although this is not always possible, it only works for our inventory examples. For the inventory Update method used in method 1, transactions need to view the current inventory number, calculate the new value based on the number of shirts sold, and then update the number of shirts. It is possible to complete the work in one step by counting updates relative to the current number of shirts. As shown below:
T1 Sales 1 Sell 3 Shirts
T2 Sales Staff 1 reduced the shirt count by 3:
UPDATE inventory SET quantity = quantity-3 WHERE item = "Shirt"
T3 Sales 2 sell 2 Shirts
T4 Sales staff 2 reduced the shirt count by 2:
UPDATE inventory SET quantity = quantity-2 WHERE item = "Shirt"
Therefore, there is no need for a transaction of multiple statements at all, and there is no need to lock the table to simulate transactional functionality. If the transaction type used is similar to this, you can do the work without transaction processing. The above example illustrates how to avoid the need for transactional functionality in special situations. But this is not to say that there is no such thing as a real need for transactional functions. A typical example is financial transfers, where money is transferred from one account to another. If Bill gave Bob a check for $, Bob cashed the check. Bill's account should have lost the dollar and Bob's account should have increased the same amount:
UPDATE account SET balance = balance-100 WHERE name = "Bill"
UPDATE account SET balance = Balance +100 WHERE name = "Bob"
If the system crashes in the execution of these two statements, the transaction is incomplete. A database system with real transaction processing and commit/rollback capabilities can handle this situation, at least theoretically. You may still have to judge which transactions are encountered and republish them, but at least you will not worry about half the transaction. In MySQL, the state of the transaction can be judged by checking the update log when the system crashes, although this may require some manual checking of the log.
Foreign key and referential integrity. A foreign key allows you to define a key in one table that is related to a key in another table, and referential integrity allows you to constrain what can be done to a table that contains a foreign key. For example, the score table in the SAMP_DB sample database contains a student_id column that we use to associate the credit records with the students in the student table. SCORE.STUDENT_ID will be defined as a foreign key in the database that supports this concept, and we will add a constraint on it so that you cannot enter credit records for students who do not exist in the student table. In addition, cascading deletes should be allowed so that if a student is deleted from the student table, any credit records for that student will automatically be removed from the
The score table is deleted.
The foreign key helps to keep data consistent and provides some convenient means. The reason why MySQL does not support foreign keys is that it has a negative effect on the implementation and maintenance of the database. (The MySQL Reference guide lists these reasons in detail.) Note that this view of the foreign key is somewhat different from other database documents, and some database documents usually describe them as "basic". MySQL developers do not agree with this view. If you agree, it is best to consider using other databases that provide support for foreign keys. If the data has a particularly complex relationship, you may not want to assume the task of implementing these dependencies in your application. (even if the effort to do so is slightly less than adding several additional delete statements.) MySQL does not support foreign keys in addition to the ability to analyze the foreign key clause in the CREATE TABLE statement to some extent. (This helps make it easier to migrate code from other databases to MySQL.) MySQL does not force a foreign key to be a constraint, nor does it provide cascading deletions.
The constraints imposed by the foreign key are generally not difficult to implement with program logic. Sometimes, it's just a matter of how data entry is handled. For example, in order to insert a new record into the score table, it is not possible to insert credits for students who do not exist. Obviously, the method of entering a group of credits should be based on the list of students drawn from the student table, taking credit for each student and using the student's ID number to produce a score table record. For this process, there is no possibility of inputting a record of a non-existent student. You don't create a credit record to insert a score table. To implement the cascading effect of delete, you must do it with your own application logic. If you want to delete student number 13th. This also implies that the student's credit record needs to be deleted. In a database that supports cascading deletes, you can delete the records of the student table and the corresponding score table records by simply using the following statement:
DELETE from student WHERE student_id = 13
In MySQL, you must explicitly use the DELETE statement to make the second deletion statement yourself:
DELETE from student WHERE student_id = 13
DELETE from score WHERE student_id = 13
Stored procedures and triggers. Stored procedures are SQL code that is compiled and stored in the server. It can be invoked at a later time without sending and parsing from the client. You can make changes to a procedure to affect any client application that uses it. The trigger feature enables a procedure to be activated when an event occurs, such as when a record is deleted from a table. For example, when a record that is a cumulative component is deleted, the cumulative number should be renewed to reflect the current situation. The stored procedure language is included in the plan that MySQL is ready to implement.
View A view is a logical concept that functions like a table but is not a table itself. It provides a way to view columns in different tables as if they belonged to the same table. A view is sometimes called a virtual table. MySQL is also ready to implement the view feature.
Record-level permissions and locks. MySQL supports various permissions, from global permissions to databases, tables, columns. However, it does not support record-level permissions. However, the Get_lock () and r e L e _lock () functions can be used in the application to implement cooperative record locking. This process is described in Appendix C, "Run operator and Function Reference", under the corresponding item.
"--" as the beginning of the comment. MySQL does not support this annotation style because it is an ambiguous structure, although annotations can be started with two dashes and a single space since MySQL3.23. For more detailed information, see 3. Section 7, "Add Notes".