需要使用PREPARE来进行。生成一个字符串,这个字符串根据2个参数, str=concat("update game set ' , 列名name, ' = ' , 值value ,'where gid = id' 然后使用PREPARE, EXECUTE来执行这个字符串中的语句。
这个我知道 按这个写的话 PREPARE updateSQL FROM 'update game set ? = ? where gid = ?'; set @a ='gname', @b = '西游志',@c = 3; EXECUTE updateSQL USING @a,@b,@c; 当时我这样不行
str=concat("update game set ' , 列名name, ' = ' , 值value ,'where gid = id' PREPARE updateSQL FROM str
经过测试可以用update来实现啊: mysql> create procedure tt_csdn (in v_name varchar(20),in v_showname varchar(20)) -> begin -> update tt set showname = v_showname where name = v_name; -> end; -> // Query OK, 0 rows affected (0.01 sec)mysql> select name,showname from tt where name = 'QQ'; -> // +------+----------+ | name | showname | +------+----------+ | QQ | QQUPDATE | +------+----------+ 1 row in set (0.00 sec)mysql> call tt_csdn('QQ','QQCSDN')// Query OK, 1 row affected (0.03 sec)mysql> select name,showname from tt where name = 'QQ'// +------+----------+ | name | showname | +------+----------+ | QQ | QQCSDN | +------+----------+ 1 row in set (0.00 sec)
然后使用PREPARE, EXECUTE来执行这个字符串中的语句。
PREPARE updateSQL FROM 'update game set ? = ? where gid = ?';
set @a ='gname', @b = '西游志',@c = 3;
EXECUTE updateSQL USING @a,@b,@c;
当时我这样不行
PREPARE updateSQL FROM str
mysql> create procedure tt_csdn (in v_name varchar(20),in v_showname varchar(20))
-> begin
-> update tt set showname = v_showname where name = v_name;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)mysql> select name,showname from tt where name = 'QQ';
-> //
+------+----------+
| name | showname |
+------+----------+
| QQ | QQUPDATE |
+------+----------+
1 row in set (0.00 sec)mysql> call tt_csdn('QQ','QQCSDN')//
Query OK, 1 row affected (0.03 sec)mysql> select name,showname from tt where name = 'QQ'//
+------+----------+
| name | showname |
+------+----------+
| QQ | QQCSDN |
+------+----------+
1 row in set (0.00 sec)