Mysql create view with multiple tables1/26/2024 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. The UNDEFINED is the default algorithm when you create a view without specifying the ALGORITHM clause or you explicitly specify ALGORITHM=UNDEFINED. 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.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |