![]() ![]() In addition, when you create a view with ALGORITHM = MERGE and MySQL can only process the view with a temporary table, MySQL automatically sets the algorithm to UNDEFINED and generates a warning. ![]() Similarly to a table, you can CREATE, ALTER, or DROP a. You can also INSERT, UPDATE, and DELETE records in a view as a table in some cases. Whenever the base tables used in the view get updated, it updates the view too. The UNDEFINED is the default algorithm when you create a view without specifying the ALGORITHM clause or you explicitly specify ALGORITHM=UNDEFINED. The records in a view are the result of a query inside it. Note that TEMPTABLE views cannot be updatable. Then, execute the input query against the temporary table.īecause MySQL has to create the temporary table to store the result set and moves the data from the base tables to the temporary table, the algorithm TEMPTABLE is less efficient than the MERGE algorithm.First, create a temporary table to store the result of the SELECT in the view definition. ![]() When you issue a query to a TEMPTABLE view, MySQL performs these steps: Convert askterisk (*) to a list column names customerName, firstName, lastName, phone, which corresponds to customerName, contactFirstName, contactLastName, phone.ĬustomerName LIKE '%Co%' Code language: SQL (Structured Query Language) ( sql ) TEMPTABLE.Convert view name contactPersons to table name customers.WHERE customerName LIKE '%Co%' Code language: SQL (Structured Query Language) ( sql ) Suppose that you issue the following statement: SELECT * FROM contactPersons The following statement creates a view based on the customers table with the name contactPersons with the MERGE algorithm: CREATE ALGORITHM= MERGE VIEW contactPersons(įROM customers Code language: SQL (Structured Query Language) ( sql ) See the following customers from the sample database: Note that the combination of input query and the SELECT statement of the view definition into a single query is referred to as view resolution. Then, execute the combined query to return the result set.First, merge the input query with the SELECT statement in the view definition into a single query.When you query from a MERGE view, MySQL processes the following steps: Here is the CREATE VIEW statement with the ALGORITHM clause: CREATE VIEWĪS select- statement Code language: SQL (Structured Query Language) ( sql ) MERGE The CREATE VIEW and ALTER VIEW statements have an optional clause: ALGORITHM. The algorithm determines how MySQL process a view and can take one of three values MERGE, TEMPTABLE, and UNDEFINE. Summary: in this tutorial, you will learn about MySQL view processing algorithms including MERGE, TEMPTABLE, and UNDEFINED. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |