Note: This article uses the mysql5.5 version as an example. The students who have done database development should not be unfamiliar with view. I have access to the most views of the application scenario has two: 1) for permission issues, in order to limit visitors to see too many table fields (or content), create a view for TA to use. 2) You can also combine multiple tables into one view for ease of query. For example, I recently encountered a requirement: there are four types of user-type tables, some of the same information, and of course there will be a lot of different fields: T_user_person (ID, Person_name, Create_time, ...) T_user_team (ID, Team_name, Create_time, ...) T_user_company (ID, Company_Name, Create_time, ...) T_user_organization (ID, organization_name, create_time, ...) there is also a business table that brings together the business data created by these four types of users: t_bussiness (ID, user_id, user _type, CONTENT, ...) User_type is the enumeration type: Who, TEAM, company, organization, which user is associated by user_id and User_type, creates this business data. The diagram is probably like this:
I was the first one to think about it. Use views? Create a view to manage all users uniformly, then query the business table, how clear the business logic! So, I built this view, as follows: Create OR REPLACE ViewView_all_type_userAsselect ID ID, person_name user_name, ' person ' user_type, create_time create_time from t_user_personunion ALLselect ID as ID, company_name as user_name, ' company ' as User_type, Create_time create_time from t_user_companyunion allselect ID as I D, organization_name as user_name, ' ORGANIZATION ' as User_type, Create_time create_time from T_user_organizationunion all Select ID as ID, team_name as user_name, ' TEAM ' as User_type, Create_time create_time from T_user_team; The diagram after the change is probably this:
The SQL statements for the Federated query are as follows: Select b.ID, b.user_id, V.user_name, B.contentfrom t_bussiness bleft join View_all_type_user v on b.user_id= v.user_id and B.user_type=v.user_typeorder by b.id Desc looks very concise, but running upIt's too slow.。 In fact, the amount of data is not large, the user has a total of about 120,000, business data about 1400. However, the speed is not tolerable after the query of the view and the table, which I think is the result of the multiple Cartesian product. In addition to the low efficiency of joint queries,view is not yet indexed, there is no way to effectively improve query efficiency. Over and over again, I decided to abandon the view, preferring to use a slightly more complex case-then statement. The effect is still possible. Select B.id,b.user_id,b.user_type,case b.user_typewhen ' person ' then (select user_name from T_user_person u where u.id=b. USER_ID) when the ' ENTERPRISE ' then (select Company_Name from T_user_company o where o.id=b.user_id) is ' CHARITY ' then (select Organization_name from t_user_organization C where c.id=b.user_id) when ' TEAM ' then (select Team_name from T_user_team T W Here t.id=b.user_id) End as User_name,b.contentfrom t_bussiness border by b.id Desc However, in this case, there is a problem:If an alias is used as a query condition, an error occurs: Unknown column ' user_name ' in ' WHERE clause ' 1) Place the query criteria for the alias behind the table: http://bbs.csdn.net/topics/320013418 This approach does not work for me and does not know if it is a version of the problem ...... 2) with a SELECT package: http://blog.csdn.net/testcs_dn/article/details/69400269 Note: Each table, including the final () of the virtual table will have to start with an individual name 3) Use the full case-then:https://www.cnblogs.com/xiandedanteng/archive/2013/09/22/3333469 in the query conditions. The SQL statement for HTML in this article should be: Select B.id,b.user_id,b.user_type,case b.user_typewhen ' person ' then (select user_name from T_user_ Person u where u.id=b.user_id) when the ' ENTERPRISE ' then (select Company_Name from T_user_company o where o.id=b.user_id) when ' CHARITY ' then (select Organization_name from t_user_organization C where c.id=b.user_id) when the ' TEAM ' then (select Team_nam E from T_user_team t where t.id=b.user_id) end as User_name,b.contentfrom t_bussiness bwherecase b.user_typewhen ' person ' t Hen (select user_name from T_user_person u where u.id=b.user_id) while ' ENTERPRISE ' then (select Company_Name from T_user_co Mpany o where o.id=b.user_id) when the ' CHARITY ' then (select Organization_name from t_user_organization C where C.I.D=B.USER_ID) when the ' TEAM ' then (select Team_name from T_user_team T where t.id=b.user_id) the end like ' Test% ' order by b.ID DESC&NB SP;------------------- However, the last day, and encountered a problem ...... new needs to come, in the outside another layer of query: there are a number of similar businesses, want to put together to sort of show. (This time I Learned "smart", regardless of the view) so, you need to nest two layers of case-then statement ..., do not consider the efficiency problem, so that the long-winded SQL readability and maintenance is too bad? Again and again, I decided to add a user_name field to the business table, which is a redundant field, but makes logic simpler and easier to maintain. As for the USER_NAME data consistency problem, there are many solutions: trigger/Asynchronous Notification/synchronous update, etc., can be implemented. After all, user_name is not always modified. The final business table is like this (of course, other business tables have also been added to the User_name field): T_bussiness (ID, user_id, User_type,user_name, CONTENT, ...) In the end it looks like this:
At least, with my current limited knowledge, this is the best solution, I hope the students have a better way to enlighten.
Some summary of the "original" view of MySQL
