mysql执行字符串sql语句_MYSQL存储过程执行用字符串拼成的sql语句

执行的sql必须定义为 @sql  才能执行  否则报错

DROP PROCEDURE IF EXISTS  test_prepareStam;

delimiter //

CREATE PROCEDURE test_prepareStam(in id int)

BEGIN

TRUNCATE TABLE test_table;

/*

-- 直接拼sql  然后prepare EXECUTE

set @exeSql=concat("INSERT INTO test_table (id, val, uv, visitor_val, visitor_uv)VALUES (",id);

set @exeSql=concat(@exeSql,", 3,4,6,7)");

-- select @exeSql;

prepare stmt from @exeSql;

EXECUTE stmt;

deallocate prepare stmt;

*/

/*

-- 直接拼sql+?   然后prepare EXECUTE+using

set @_id=id;

set @exeSql=concat("INSERT INTO test_table (id, val, uv, visitor_val, visitor_uv)VALUES (?, 3,4,6,7)");

prepare stmt from @exeSql;

EXECUTE stmt using @_id;

deallocate prepare stmt;

*/

INSERT INTO test_table (id, val, uv, visitor_val, visitor_uv)VALUES (111, 3,4,6,7);

INSERT INTO test_table (id, val, uv, visitor_val, visitor_uv)VALUES (112, 3,4,6,7);

select * from test_table;

END;

//

-- TRUNCATE TABLE test_table;

-- call test_prepareStam(150);

-- select * from test_table;