MySQL 5:尝试生成动态序列 ID 作为存储函数或存储过程
我在 OS X - Snow Leopard 上使用 MySQL 5...
准备好工作代码,从序列表中获取最高序列号 ID,然后递增并将其分配给其相应的表:
原始代码的目的是动态递增特定的序列号。表的最后一个序列 id 并将其相应表的 id 设置为该新值。
注释:
1.原始代码片段(正在运行):
获取最后一个序列号
replace into my_sequence_id_s set id =
(select max(CONVERT(sequence_id, signed)) from my_table_t);
增加数字
insert into my_sequence_id_s set id = null;
将数字保存为变量
set @dynamicId = last_insert_id();
打印
select @dynamicId;
2。重构:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id
(IN _sequence_table varchar(40),
IN _actual_table varchar(40),
IN _id_field VARCHAR(40),
OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL =
concat('REPLACE INTO ', _sequence_table, 'SET ID =
(select max(CONVERT(',_id_field,', signed))
from ', _actual_table, ');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL =
concat('insert into ', _sequence_table ,' set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
3.这是调用函数(失败):
-- 获取动态递增的 id
call generate_dynamic_id(
'my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
错误:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1
由于某些奇怪的原因,存储函数或触发器中不允许动态函数调用,因此这就是存储函数的原因使用了程序。
正如您所看到的,我在参数处设置 varchar,然后尝试将它们连接为字符串并在准备好的语句中运行它们。
任何帮助将不胜感激...
Am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from a sequence table and then increments and assigns it to its corresponding table:
The original code's purpose is to dynamically increments a specific table's last sequence id and set its corresponding table's id to that new value.
Notes:
1. Original Code Snippet (which is working):
Get last sequence number
replace into my_sequence_id_s set id =
(select max(CONVERT(sequence_id, signed)) from my_table_t);
Increments the number
insert into my_sequence_id_s set id = null;
Saves the number as a variable
set @dynamicId = last_insert_id();
select @dynamicId;
2. Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id
(IN _sequence_table varchar(40),
IN _actual_table varchar(40),
IN _id_field VARCHAR(40),
OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL =
concat('REPLACE INTO ', _sequence_table, 'SET ID =
(select max(CONVERT(',_id_field,', signed))
from ', _actual_table, ');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL =
concat('insert into ', _sequence_table ,' set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
3. Here's the calling function (which fails):
-- Get dynamically incremented id
call generate_dynamic_id(
'my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1
For some odd reason, dynamic function calls are not allowed in Stored Functions or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying to concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
concat('REPLACE INTO ', _sequence_table, 'SET ID =
_sequence_table 和 SET ID 之间有空格吗?
concat('REPLACE INTO ', _sequence_table, 'SET ID =
where space between _sequence_table and SET ID?