当前位置: 代码迷 >> MySQL >> MySQL表数据比较跟Object全文搜索
  详细解决方案

MySQL表数据比较跟Object全文搜索

热度:135   发布时间:2016-05-05 17:14:02
MySQL表数据比较和Object全文搜索
  • ?比较MySQL表中的数据:先需要过滤排除不需要比较的字段,比如timestamp字段等;然后再排序字段(先按关键字排序,再按非空的字段排序,最后按其他字段排序), 当然一般情形还需要加where 条件,下面写了一个函数来构造一个SQL 语句。
    CREATE DEFINER=`mysql`@`%` FUNCTION `f_toolkit_getFieldListFromTable`(`databaseName` varchar(255),`tableName` varchar(255),`is_RemoveTimestampField` boolean,excludeFiledlist varchar(1024)) RETURNS varchar(1024) CHARSET latin1BEGIN	DECLARE exception_occured TINYINT default 0;	DECLARE curr_field varchar(255);  DECLARE curr_type varchar(255);	DECLARE curr_key  varchar(255);  DECLARE curr_isnull varchar(10);  DECLARE key_field_limit int DEFAULT 5;	DECLARE TMP_CUR CURSOR FOR                        select column_name, column_type,column_key,is_nullable from information_schema.columns where table_schema=trim(databaseName) and table_name=trim(tableName) and  locate(binary upper(column_name),binary upper(excludeFiledlist)) = 0                        order by column_key desc,IS_NULLABLE,ORDINAL_POSITION; 		DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,NOT FOUND SET exception_occured = 1;	set @result = '';	set @orderBy = '';	set @nonNull = '';	set @nonNull_no = 0;	set @nonKey_no = 0;	set @nonKeyFields = '';	OPEN TMP_CUR;	LOOP_LABLE:LOOP    	   FETCH TMP_CUR into curr_field,curr_type,curr_key,curr_isnull;	   IF exception_occured = 1 THEN              	      LEAVE LOOP_LABLE;	   END IF; 		 IF !(is_RemoveTimestampField && curr_type like 'timestamp') then 			 if @result = '' then 					set @result = curr_field;			 else					set @result = CONCAT(@result,',',curr_field);       end if;						set @nonKey_no = @nonKey_no + 1;			if @nonKeyFields  = '' then 				set @nonKeyFields = curr_field;			else 			 if @nonKey_no < key_field_limit then 					set @nonKeyFields = concat(@nonKeyFields,',',curr_field);			 end if;		 end if;		 if lower(curr_key) in ('pri','mul','uni') then 				if @orderBy = '' then 					 set @orderBy = curr_field;				else					 set @orderBy = CONCAT(@orderBy,',',curr_field);				end if;		 end if;		 if lower(curr_isnull) = 'no' && @nonNull_no < key_field_limit then 				if @nonNull = '' then 						set @nonNull = curr_field;				else						set @nonNull = CONCAT(@nonNull,',',curr_field);				end if;				set @nonNull_no = @nonNull_no + 1;						 end if;						 END IF;	END LOOP LOOP_LABLE;         	CLOSE TMP_CUR; 	if @orderBy = '' then 		if @nonNull = '' then			set @orderBy = @nonKeyFields;		else		  set @orderBy = @nonNull;		end if;  end if;	RETURN CONCAT('select ', @result , ' from ', databaseName, '.', tableName, ' order by ', @orderBy);END
    ?
  • 常常要全文搜索object(如Field,table,view,procedure)被其他object引用(这里是包含或用到的意思),写了一个存储过程来完成(需要MySQL 5.1 及以上支持)
    CREATE DEFINER=`mysql`@`%` PROCEDURE `P_lookup_allObject_isUsing_Some_object`(`object` varchar(255))BEGIN  set @v_obj_orginal = `object`;  set `object` = concat('[^a-zA-Z_0-9]',ifnull(`object`,'null'),'[^a-zA-Z_0-9]');  select name from (        select concat(event_SCHEMA,'.',event_NAME) as name, 'event' as type from information_schema.events where EVENT_DEFINITION regexp `object`  union   select concat(TRIGGER_SCHEMA,'.',TRIGGER_NAME) as name,'trigger' as type from information_schema.triggers where ACTION_STATEMENT regexp `object` or  ACTION_CONDITION regexp `object`  union select concat(ROUTINE_SCHEMA,'.',ROUTINE_NAME) as type, ROUTINE_TYPE from information_schema.routines where ROUTINE_DEFINITION regexp `object` union select concat(TABLE_SCHEMA,'.',TABLE_NAME) as name, 'view' as type from information_schema.views where VIEW_DEFINITION regexp `object` union select concat(db,'.',`name`) as name, type from mysql.proc where body regexp `object` union select concat(TABLE_SCHEMA,'.',TABLE_NAME) as name, 'column' as type from information_schema.`COLUMNS` where COLUMN_NAME like @v_obj_orginal union select concat(db,'.',`name`) as name, 'event' as type from mysql.`event` where body regexp `object`) as Q order by type, name;END
    ?
  相关解决方案
本站暂不开放注册!
内测阶段只得通过邀请码进行注册!