参考语法 http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
sql 片段 记录下
DELIMITER $$
USE `portal_group`$$
DROP PROCEDURE IF EXISTS `proc_sync_data`$$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_sync_data`(
)
SQL SECURITY INVOKER
BEGIN
DECLARE cur2_tag CURSOR FOR SELECT id ,entity_name,data_from,data_to FROM sync_table WHERE data_from = 'group';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done_cursor = 1 ;
OPEN cur2_tag;
SET done_cursor = 0;
FETCH cur2_tag INTO var_id,var_entity_name,var_data_from,var_data_to;
WHILE (done_cursor = 0) DO
BEGIN
IF (fc_sync_data(var_entity,var_data_from_db)='1') THEN
SET @execSql = CONCAT('select count(*) into @records from ',var_data_from_db_table,';'); -- 变量 做 表名
PREPARE stmt1 FROM @execSql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET num = @records; -- 从结果中获取 执行的记录数
IF (num>0) THEN
SET @execSql = CONCAT('truncate table ',var_data_to_db_table,';'); -- 变量 做 表名
PREPARE stmt1 FROM @execSql;
EXECUTE stmt1;
SET @execSql = CONCAT('INSERT INTO ',var_data_to_db_table ,' SELECT * FROM ',var_data_from_db_table,';');
PREPARE stmt1 FROM @execSql;
EXECUTE stmt1;
END IF;
END IF;
END;
SET done_cursor = 0 ;
FETCH cur2_tag INTO var_id,var_entity_name,var_data_from,var_data_to;
END WHILE;
CLOSE cur2_tag;
END$$
DELIMITER ;
字符函数 http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh 12. 函数和操作符
-- mysql 字符串函数
-- 去空格
SELECT LENGTH(TRIM(' 123456 '))
6
-- 索引每个字符时 会用到 大小写
SELECT ASCII('a')<ASCII('z')
1
SELECT ASCII('Z'); 90
SELECT ASCII('A'); 65
SELECT ASCII('a'); 97
SELECT ASCII('z'); 122
-- 替换函数
SELECT REPLACE('ABCDEFGHC','C','123')
AB123DEFGH123
-- 截取
SELECT SUBSTRING('1234567890',5,1)
5
SELECT SUBSTRING('1234567890',-5,1)
6