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