dev/2020/2020-012(OK)/2020-012-xpp-001.sql

USE DCP;
ALTER TABLE `dcp`.`base_unit_view`
MODIFY COLUMN `project_performance` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '项目业绩' AFTER `core_technology`;

ALTER TABLE `dcp`.`base_unit_info`
MODIFY COLUMN `dynamic` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '动态' AFTER `unit_address`;
/*  以上已经发现场在生产环境执行  2020-10-23 */
SET GLOBAL log_bin_trust_function_creators=1;
/*  以上已经发现场在生产环境执行  2020-10-23 */

DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty text(0) )
RETURNS text(0)
DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;
/*  以上已经发现场在生产环境执行  2020-10-23 */
update base_unit_info set dynamic = fnStripTags(unit_introdution)  where unit_introdution is not null and dynamic is null;
update base_unit_view set project_performance = fnStripTags(unit_introdution)  where  unit_introdution is not null and project_performance is null;
/*  以上已经发现场在生产环境执行  2020-10-23 */
-- 替换 &nbsp; 为空格  560  547
UPDATE `base_unit_view` SET project_performance = REPLACE ( project_performance, '&nbsp;', ' ' ) WHERE project_performance LIKE '%&nbsp;%';
UPDATE `base_unit_info` SET dynamic = REPLACE ( dynamic, '&nbsp;', ' ' ) WHERE DYNAMIC LIKE '%&nbsp;%';
-- 替换 &quot; 为双引号   380    369
UPDATE `base_unit_view` SET project_performance = REPLACE ( project_performance, '&quot;', '"' ) WHERE project_performance LIKE '%&quot;%';
UPDATE `base_unit_info` SET dynamic = REPLACE ( dynamic, '&quot;', '"' ) WHERE DYNAMIC LIKE '%&quot;%';
-- 替换 &#39; 为单引号   290    280
UPDATE `base_unit_view` SET project_performance = REPLACE ( project_performance, '&#39;', "'" ) WHERE project_performance LIKE '%&#39;%';
UPDATE `base_unit_info` SET dynamic = REPLACE ( dynamic, '&#39;', "'" ) WHERE DYNAMIC LIKE '%&#39;%';
-- 替换 &amp; 为&   68    66
UPDATE `base_unit_view` SET project_performance = REPLACE ( project_performance, '&amp;', '&' ) WHERE project_performance LIKE '%&amp;%';
UPDATE `base_unit_info` SET dynamic = REPLACE ( dynamic, '&amp;', '&' ) WHERE DYNAMIC LIKE '%&amp;%';
/*  以上已经发现场在生产环境执行  2020-10-23 */