如何从另一个表设置AUTO_INCREMENT [英] How to set AUTO_INCREMENT from another table
本文介绍了如何从另一个表设置AUTO_INCREMENT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在另一个表的CREATE TABLE
或ALTER TABLE
上设置AUTO_INCREMENT
?
How can I set the AUTO_INCREMENT
on CREATE TABLE
or ALTER TABLE
from another table?
我找到了这个问题,但没有解决我的问题: 如何重置MySQL自动增量使用另一个表中的MAX值?
I found this question, but not solved my issue: How to Reset an MySQL AutoIncrement using a MAX value from another table?
我也尝试过:
CREATE TABLE IF NOT EXISTS `table_name` (
`id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
`columnOne` tinyint(1) NOT NULL,
`columnTwo` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=(SELECT `AUTO_INCREMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'another_table_name');
此:
ALTER TABLE `table_name` AUTO_INCREMENT=(SELECT `AUTO_INCREMENT` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'another_table_name');
此:
CREATE TABLE IF NOT EXISTS `table_name` (
`id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
`columnOne` tinyint(1) NOT NULL,
`columnTwo` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=(SELECT (MAX(`id`)+1) FROM `another_table_name`);
和这个:
ALTER TABLE `table_name` AUTO_INCREMENT=(SELECT (MAX(`id`)+1) FROM `another_table_name`);
推荐答案
此代码将为您创建过程:
This code will create procedure for you:
CREATE PROCEDURE `tbl_wth_ai`(IN `ai_to_start` INT)
BEGIN
SET @s=CONCAT('CREATE TABLE IF NOT EXISTS `table_name` (
`id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
`columnOne` tinyint(1) NOT NULL,
`columnTwo` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT = ', `ai_to_start`);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
然后您可以调用CALL tbl_wth_ai(2);
并将参数传递到方括号内.
Then you may call CALL tbl_wth_ai(2);
passing the parameter inside the brackets.
例如:
CALL tbl_wth_ai((SELECT id FROM `ttest` WHERE c1='b'));
这篇关于如何从另一个表设置AUTO_INCREMENT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文