如何取消设置 MyISAM 表上的 MAX_ROWS 表选项？

发布于 2024-12-25 04:22:23 字数 360 浏览 0 评论 0原文

我有一个这样定义的表：

CREATE TABLE `_debug_log` (
  ...
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED;

我想删除 MAX_ROWS 选项，因为该表不需要它，但我确实需要保留现有数据。有没有办法通过 ALTER 语句取消设置此表选项，或者我需要导出数据，重建表，然后将数据导入回？我检查了有关 MAX_ROWS 的 MySQL 文档，但它只说它需要一个正整数值，而没有说明默认值是什么或 0 值可能会做什么。

原文

I have a table defined as such:

CREATE TABLE `_debug_log` (
  ...
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED;

I'd like to drop the MAX_ROWS option as it isn't necessary for this table, but I do need to keep the existing data. Is there a way to unset this table option via an ALTER statement, or will I need to export the data, rebuild the table and then import the data back in? I checked the MySQL docs on MAX_ROWS, but it only says it requires a positive integer value and doesn't say what the default value is or what a value of 0 might do.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

弱骨蛰伏 2025-01-01 04:22:23

对于它的价值：

CREATE TABLE `_debug_log` (
id int unsigned primary key auto_increment
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED;

SHOW CREATE TABLE _debug_log;

给出：

CREATE TABLE `_debug_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED

然后：

alter table _debug_log max_rows = 0;

SHOW CREATE TABLE _debug_log;

给出：

CREATE TABLE `_debug_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

我尝试在运行ALTER之前和运行ALTER之后插入一些数据，它似乎没有受到影响。

此外，如果您使用相同的选项创建相同的表，但排除 MAX_ROWS 选项，则插入相同的数据，然后在 information_schema.tables 中查看结果数据数据几乎相同（除了明显的差异）：

For what it is worth:

CREATE TABLE `_debug_log` (
id int unsigned primary key auto_increment
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED;

SHOW CREATE TABLE _debug_log;

gives:

CREATE TABLE `_debug_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=100000 ROW_FORMAT=COMPRESSED

then:

alter table _debug_log max_rows = 0;

SHOW CREATE TABLE _debug_log;

gives:

CREATE TABLE `_debug_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=896692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

I tried inserting some data before running the ALTER and after running the ALTER and it did not seem to be affected.

Furthermore, if you create the same table with the same options but exclude the MAX_ROWS option, insert the same data and then take a look at the resulting data in information_schema.tables the data is pretty much identical (obvious differences aside):

回复收藏 0 原文