MYSQL 的常用操作

MYSQL 的常用操作

字符串前截掉

update Acr_Store set CarCodes= substring(CarCodes,2) where CarCodes like ';%';

字符串后截掉

update Acr_Store set CarCodes= left(CarCodes,length(CarCodes)-1) where
CarCodes like ‘%;’;

存储过程删除、创建

#先清理数据
truncate table cache_base_datas;

DELIMITER $$
DROP PROCEDURE IF EXISTS CacheData$$
create PROCEDURE CacheData()
begin
    DECLARE done INT DEFAULT FALSE;
    DECLARE _outid INT;
    DECLARE sor CURSOR 
    FOR 
    SELECT AcrStoreTypeId FROM Acr_Store_Type;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN sor;
   read_loop: LOOP
    FETCH sor INTO _outid;

    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO cache_base_datas
(`TableName`,`OperateType`,`DataId`,`ChangeTime`,`Remark`,`CacheType`)
VALUES('Acr_Store_Type',0,_outid,now(),'','table');

    END LOOP;
    CLOSE sor ;
  end$$          
DELIMITER ;
CALL CacheData;
DROP PROCEDURE IF EXISTS CacheData;