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

来源：互联网发布：web编程语言编辑：程序博客网时间：2024/06/08 12:14

执行的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;

1 0