mysql – 如何从另一个表设置AUTO_INCREMENT

前端之家收集整理的这篇文章主要介绍了mysql – 如何从另一个表设置AUTO_INCREMENT前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

如何在另一个表上的CREATE TABLE或ALTER TABLE上设置AUTO_INCREMENT?

我发现了这个问题,但没有解决我的问题:
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,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`);
最佳答案
代码将为您创建过程:

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,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);在括号内传递参数.

例如:

CALL tbl_wth_ai((SELECT id FROM `ttest` WHERE c1='b'));
原文链接:https://www.f2er.com/mysql/433241.html

猜你在找的MySQL相关文章