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

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 latin1
BEGIN
	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

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

相关推荐