mysql视图语句解析

例句:

Create View
CREATE ALGORITHM=UNDEFINED DEFINER=hck@192.168.%.% SQL SECURITY DEFINER VIEW view_activity_coupon_record_grid AS
select ra.ra_title AS ra_title, ra.ra_start_date AS ra_start_date, ra.ra_end_date AS ra_end_date, ra.ra_is_official AS ra_is_official, rac.rac_type AS rac_type, rac.rac_floor_money AS rac_floor_money, rac.rh_id AS rh_id, rac.rac_begin_date AS rac_begin_date, rac.rac_past_date AS rac_past_date, rac.rhr_id AS rhr_id, rac.rhrt_id AS rhrt_id, rac.rac_lease_month AS rac_lease_month, rac.rac_money AS rac_money, rac.rac_discount AS rac_discount, rac.rac_monthly_rent AS rac_monthly_rent, ra.ra_key AS ra_key, rgac.mua_id AS mua_id, rgac.rgac_secret_key AS rgac_secret_key, rgac.rhr_id AS astrict_rhr_id, rgac.rgac_is_use AS rgac_is_use, rac.rac_key AS rac_key, rgac.rgac_key AS rgac_key, rac.pc_id AS pc_id, ra.ra_state AS ra_state, rac.rac_state AS rac_state, rgac.rgac_state AS rgac_state, ra.ra_status AS ra_status from ((r_activity ra left join r_activity_coupon rac on ((ra.ra_key = rac.ra_id))) left join r_get_activity_coupon rgac on ((rac.rac_key = rgac.rac_id)))
ALGORITHM=UNDEFINED：指定视图的处理算法；

DEFINER=root@localhost：指定视图创建者；

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；

任意用户X访问此VIEW时，能否成功取决于X是否有调用该VIEW的权限，以及definer是否有view中的SELECT的权限。

只需要修改创建同名用户或者修改definer即可。

alter DEFINER = ‘xx’@‘localhost’ view xxxx as ……

ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。

如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED，MySQL自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，

这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

DEFINER 表示按定义者拥有的权限来执行

INVOKER 表示用调用者的权限来执行。默认情况下，系统指定为DEFINER