如何从另一个表设置 AUTO_INCRMENT

我怎样才能设置AUTO_INCREMENT on CREATE TABLE or ALTER TABLE从另一张桌子？

我发现了这个问题，但没有解决我的问题：如何使用另一个表中的最大值重置 MySQL 自动增量？ https://stackoverflow.com/questions/2410689/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');

this:

ALTER TABLE `table_name` AUTO_INCREMENT=(SELECT `AUTO_INCREMENT` FROM  `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'another_table_name');

this:

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`);

此代码将为您创建过程：

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);传递括号内的参数。

例如：

CALL tbl_wth_ai((SELECT id FROM `ttest` WHERE c1='b'));