如题

场景

将 discuzX3.4 的数据从阿里云自建数据库导出来，然后导入到 RDS。因为阿里云的 RDS 不支持 MyISAM 和 MEMORY 存储引擎，所以报如下错误

1
2
3
4
5
6
7
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
ERROR 3161 (HY000): Storage engine MEMORY is disabled (Table creation is disallowed).
ERROR 1146 (42S02): Table 'hellojz.forum_threaddisablepos' doesn't exist

ERROR 3161 (HY000): Storage engine MEMORY is disabled (Table creation is disallowed).
ERROR 1146 (42S02): Table 'hellojz.ucenter_vars' doesn't exist
...

查看了 RDS 的官方文档，声称 MyISAM 会自动转为 InnoDB，确实已经将绝大部分表自动转了，但是依然有部分为 MyISAM 的表未自动转换，同时 discuz 还有表使用的 MEMORY 的存储引擎，所以，导入数据以失败告终。
RDS for MySQL默认关闭MyISAM引擎

原文：MyISAM引擎表不支持事务，读写操作会相互冲突，仅支持表级别锁。当其上的查询或者写入操作时间比较长的时候，会阻塞其他操作，容易导致连接堆积，而且在crash 后存在数据丢失的风险，因此RDS for MySQL推荐使用 Innodb 引擎。
目前RDS for MySQL如果导入表、新建表是MyISAM引擎或调整表引擎为MyISAM，会自动修改为Innodb引擎。

为什么 RDS for MySQL 不支持 MyISAM 引擎？

原文：RDS for MySQL 不支持 MyISAM 引擎的主要原因有如下几个：

  • MyISAM 对数据完整性的保护存在缺陷，且这些缺陷会导致数据库数据的损坏甚至丢失。另外，这些缺陷很多是设计问题，无法在不破坏兼容性的前提下修复。
  • MyISAM 在出现数据损害情况下，很多都需要手工修复，无法适用于产品服务的方式。
  • 对于 RDS 的存储而言，MyISAM 对于 I/O 的操作不是最优化的方案，导致 MyISAM 的性能相对于 InnoDB 的优势不大。
  • MyISAM 向 InnoDB 的迁移代价低，大多数应用仅需要改动建表的代码即可完成迁移。
  • MyISAM 的发展在向 InnoDB 转移，在最新的 5.7 版本中 MySQL 可以完全不是 MyISAM，系统的数控也被转移到了 InnoDB。

更多关于 RDS 的信息

请点击：云数据库 RDS 版

批量生成修改所有表的存储引擎为 InnoDB 的脚本语句

1
2
3
4
USE dz;
SELECT CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB;') as alertScript
FROM information_schema.TABLES AS t  
WHERE TABLE_SCHEMA = 'dz' AND TABLE_TYPE = 'BASE TABLE';

生成结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
ALTER TABLE common_admincp_cmenu ENGINE=InnoDB;
ALTER TABLE common_admincp_group ENGINE=InnoDB;
ALTER TABLE common_admincp_member ENGINE=InnoDB;
ALTER TABLE common_admincp_perm ENGINE=InnoDB;
ALTER TABLE common_admincp_session ENGINE=InnoDB;
ALTER TABLE common_admingroup ENGINE=InnoDB;
ALTER TABLE common_adminnote ENGINE=InnoDB;
ALTER TABLE common_advertisement ENGINE=InnoDB;
ALTER TABLE common_advertisement_custom ENGINE=InnoDB;
ALTER TABLE common_banned ENGINE=InnoDB;
ALTER TABLE common_block ENGINE=InnoDB;
ALTER TABLE common_block_favorite ENGINE=InnoDB;
ALTER TABLE common_block_item ENGINE=InnoDB;
ALTER TABLE common_block_item_data ENGINE=InnoDB;
ALTER TABLE common_block_permission ENGINE=InnoDB;
ALTER TABLE common_block_pic ENGINE=InnoDB;
ALTER TABLE common_block_style ENGINE=InnoDB;
ALTER TABLE common_block_xml ENGINE=InnoDB;
ALTER TABLE common_cache ENGINE=InnoDB;
ALTER TABLE common_card ENGINE=InnoDB;
ALTER TABLE common_card_log ENGINE=InnoDB;
ALTER TABLE common_card_type ENGINE=InnoDB;
ALTER TABLE common_connect_guest ENGINE=InnoDB;
ALTER TABLE common_credit_log ENGINE=InnoDB;
ALTER TABLE common_credit_log_field ENGINE=InnoDB;
ALTER TABLE common_credit_rule ENGINE=InnoDB;
ALTER TABLE common_credit_rule_log ENGINE=InnoDB;
ALTER TABLE common_credit_rule_log_field ENGINE=InnoDB;
ALTER TABLE common_cron ENGINE=InnoDB;
ALTER TABLE common_devicetoken ENGINE=InnoDB;
ALTER TABLE common_district ENGINE=InnoDB;
ALTER TABLE common_diy_data ENGINE=InnoDB;
ALTER TABLE common_domain ENGINE=InnoDB;
ALTER TABLE common_failedip ENGINE=InnoDB;
ALTER TABLE common_failedlogin ENGINE=InnoDB;
ALTER TABLE common_friendlink ENGINE=InnoDB;
ALTER TABLE common_grouppm ENGINE=InnoDB;
ALTER TABLE common_invite ENGINE=InnoDB;
ALTER TABLE common_magic ENGINE=InnoDB;
ALTER TABLE common_magiclog ENGINE=InnoDB;
ALTER TABLE common_mailcron ENGINE=InnoDB;
ALTER TABLE common_mailqueue ENGINE=InnoDB;
ALTER TABLE common_member ENGINE=InnoDB;
ALTER TABLE common_member_action_log ENGINE=InnoDB;
ALTER TABLE common_member_connect ENGINE=InnoDB;
ALTER TABLE common_member_count ENGINE=InnoDB;
ALTER TABLE common_member_crime ENGINE=InnoDB;
ALTER TABLE common_member_field_forum ENGINE=InnoDB;
ALTER TABLE common_member_field_home ENGINE=InnoDB;
ALTER TABLE common_member_forum_buylog ENGINE=InnoDB;
ALTER TABLE common_member_grouppm ENGINE=InnoDB;
ALTER TABLE common_member_log ENGINE=InnoDB;
ALTER TABLE common_member_magic ENGINE=InnoDB;
ALTER TABLE common_member_medal ENGINE=InnoDB;
ALTER TABLE common_member_newprompt ENGINE=InnoDB;
ALTER TABLE common_member_profile ENGINE=InnoDB;
ALTER TABLE common_member_profile_setting ENGINE=InnoDB;
ALTER TABLE common_member_security ENGINE=InnoDB;
ALTER TABLE common_member_secwhite ENGINE=InnoDB;
ALTER TABLE common_member_stat_field ENGINE=InnoDB;
ALTER TABLE common_member_status ENGINE=InnoDB;
ALTER TABLE common_member_validate ENGINE=InnoDB;
ALTER TABLE common_member_verify ENGINE=InnoDB;
ALTER TABLE common_member_verify_info ENGINE=InnoDB;
ALTER TABLE common_member_wechat ENGINE=InnoDB;
ALTER TABLE common_member_wechatmp ENGINE=InnoDB;
ALTER TABLE common_myapp ENGINE=InnoDB;
ALTER TABLE common_myinvite ENGINE=InnoDB;
ALTER TABLE common_mytask ENGINE=InnoDB;
ALTER TABLE common_nav ENGINE=InnoDB;
ALTER TABLE common_onlinetime ENGINE=InnoDB;
ALTER TABLE common_optimizer ENGINE=InnoDB;
ALTER TABLE common_patch ENGINE=InnoDB;
ALTER TABLE common_plugin ENGINE=InnoDB;
ALTER TABLE common_pluginvar ENGINE=InnoDB;
ALTER TABLE common_process ENGINE=InnoDB;
ALTER TABLE common_regip ENGINE=InnoDB;
ALTER TABLE common_relatedlink ENGINE=InnoDB;
ALTER TABLE common_remote_port ENGINE=InnoDB;
ALTER TABLE common_report ENGINE=InnoDB;
ALTER TABLE common_searchindex ENGINE=InnoDB;
ALTER TABLE common_seccheck ENGINE=InnoDB;
ALTER TABLE common_secquestion ENGINE=InnoDB;
ALTER TABLE common_session ENGINE=InnoDB;
ALTER TABLE common_setting ENGINE=InnoDB;
ALTER TABLE common_smiley ENGINE=InnoDB;
ALTER TABLE common_sphinxcounter ENGINE=InnoDB;
ALTER TABLE common_stat ENGINE=InnoDB;
ALTER TABLE common_statuser ENGINE=InnoDB;
ALTER TABLE common_style ENGINE=InnoDB;
ALTER TABLE common_stylevar ENGINE=InnoDB;
ALTER TABLE common_syscache ENGINE=InnoDB;
ALTER TABLE common_tag ENGINE=InnoDB;
ALTER TABLE common_tagitem ENGINE=InnoDB;
ALTER TABLE common_task ENGINE=InnoDB;
ALTER TABLE common_taskvar ENGINE=InnoDB;
ALTER TABLE common_template ENGINE=InnoDB;
ALTER TABLE common_template_block ENGINE=InnoDB;
ALTER TABLE common_template_permission ENGINE=InnoDB;
ALTER TABLE common_uin_black ENGINE=InnoDB;
ALTER TABLE common_usergroup ENGINE=InnoDB;
ALTER TABLE common_usergroup_field ENGINE=InnoDB;
ALTER TABLE common_visit ENGINE=InnoDB;
ALTER TABLE common_word ENGINE=InnoDB;
ALTER TABLE common_word_type ENGINE=InnoDB;
ALTER TABLE connect_disktask ENGINE=InnoDB;
ALTER TABLE connect_feedlog ENGINE=InnoDB;
ALTER TABLE connect_memberbindlog ENGINE=InnoDB;
ALTER TABLE connect_postfeedlog ENGINE=InnoDB;
ALTER TABLE connect_tthreadlog ENGINE=InnoDB;
ALTER TABLE forum_access ENGINE=InnoDB;
ALTER TABLE forum_activity ENGINE=InnoDB;
ALTER TABLE forum_activityapply ENGINE=InnoDB;
ALTER TABLE forum_announcement ENGINE=InnoDB;
ALTER TABLE forum_attachment ENGINE=InnoDB;
ALTER TABLE forum_attachment_0 ENGINE=InnoDB;
ALTER TABLE forum_attachment_1 ENGINE=InnoDB;
ALTER TABLE forum_attachment_2 ENGINE=InnoDB;
ALTER TABLE forum_attachment_3 ENGINE=InnoDB;
ALTER TABLE forum_attachment_4 ENGINE=InnoDB;
ALTER TABLE forum_attachment_5 ENGINE=InnoDB;
ALTER TABLE forum_attachment_6 ENGINE=InnoDB;
ALTER TABLE forum_attachment_7 ENGINE=InnoDB;
ALTER TABLE forum_attachment_8 ENGINE=InnoDB;
ALTER TABLE forum_attachment_9 ENGINE=InnoDB;
ALTER TABLE forum_attachment_exif ENGINE=InnoDB;
ALTER TABLE forum_attachment_unused ENGINE=InnoDB;
ALTER TABLE forum_attachtype ENGINE=InnoDB;
ALTER TABLE forum_bbcode ENGINE=InnoDB;
ALTER TABLE forum_collection ENGINE=InnoDB;
ALTER TABLE forum_collectioncomment ENGINE=InnoDB;
ALTER TABLE forum_collectionfollow ENGINE=InnoDB;
ALTER TABLE forum_collectioninvite ENGINE=InnoDB;
ALTER TABLE forum_collectionrelated ENGINE=InnoDB;
ALTER TABLE forum_collectionteamworker ENGINE=InnoDB;
ALTER TABLE forum_collectionthread ENGINE=InnoDB;
ALTER TABLE forum_creditslog ENGINE=InnoDB;
ALTER TABLE forum_debate ENGINE=InnoDB;
ALTER TABLE forum_debatepost ENGINE=InnoDB;
ALTER TABLE forum_faq ENGINE=InnoDB;
ALTER TABLE forum_filter_post ENGINE=InnoDB;
ALTER TABLE forum_forum ENGINE=InnoDB;
ALTER TABLE forum_forum_threadtable ENGINE=InnoDB;
ALTER TABLE forum_forumfield ENGINE=InnoDB;
ALTER TABLE forum_forumrecommend ENGINE=InnoDB;
ALTER TABLE forum_groupcreditslog ENGINE=InnoDB;
ALTER TABLE forum_groupfield ENGINE=InnoDB;
ALTER TABLE forum_groupinvite ENGINE=InnoDB;
ALTER TABLE forum_grouplevel ENGINE=InnoDB;
ALTER TABLE forum_groupuser ENGINE=InnoDB;
ALTER TABLE forum_hotreply_member ENGINE=InnoDB;
ALTER TABLE forum_hotreply_number ENGINE=InnoDB;
ALTER TABLE forum_imagetype ENGINE=InnoDB;
ALTER TABLE forum_medal ENGINE=InnoDB;
ALTER TABLE forum_medallog ENGINE=InnoDB;
ALTER TABLE forum_memberrecommend ENGINE=InnoDB;
ALTER TABLE forum_moderator ENGINE=InnoDB;
ALTER TABLE forum_modwork ENGINE=InnoDB;
ALTER TABLE forum_newthread ENGINE=InnoDB;
ALTER TABLE forum_onlinelist ENGINE=InnoDB;
ALTER TABLE forum_order ENGINE=InnoDB;
ALTER TABLE forum_poll ENGINE=InnoDB;
ALTER TABLE forum_polloption ENGINE=InnoDB;
ALTER TABLE forum_polloption_image ENGINE=InnoDB;
ALTER TABLE forum_pollvoter ENGINE=InnoDB;
ALTER TABLE forum_post ENGINE=InnoDB;
ALTER TABLE forum_post_location ENGINE=InnoDB;
ALTER TABLE forum_post_moderate ENGINE=InnoDB;
ALTER TABLE forum_post_tableid ENGINE=InnoDB;
ALTER TABLE forum_postcache ENGINE=InnoDB;
ALTER TABLE forum_postcomment ENGINE=InnoDB;
ALTER TABLE forum_postlog ENGINE=InnoDB;
ALTER TABLE forum_poststick ENGINE=InnoDB;
ALTER TABLE forum_promotion ENGINE=InnoDB;
ALTER TABLE forum_ratelog ENGINE=InnoDB;
ALTER TABLE forum_relatedthread ENGINE=InnoDB;
ALTER TABLE forum_replycredit ENGINE=InnoDB;
ALTER TABLE forum_rsscache ENGINE=InnoDB;
ALTER TABLE forum_sofa ENGINE=InnoDB;
ALTER TABLE forum_spacecache ENGINE=InnoDB;
ALTER TABLE forum_statlog ENGINE=InnoDB;
ALTER TABLE forum_thread ENGINE=InnoDB;
ALTER TABLE forum_thread_moderate ENGINE=InnoDB;
ALTER TABLE forum_threadaddviews ENGINE=InnoDB;
ALTER TABLE forum_threadcalendar ENGINE=InnoDB;
ALTER TABLE forum_threadclass ENGINE=InnoDB;
ALTER TABLE forum_threadclosed ENGINE=InnoDB;
ALTER TABLE forum_threaddisablepos ENGINE=InnoDB;
ALTER TABLE forum_threadhidelog ENGINE=InnoDB;
ALTER TABLE forum_threadhot ENGINE=InnoDB;
ALTER TABLE forum_threadimage ENGINE=InnoDB;
ALTER TABLE forum_threadlog ENGINE=InnoDB;
ALTER TABLE forum_threadmod ENGINE=InnoDB;
ALTER TABLE forum_threadpartake ENGINE=InnoDB;
ALTER TABLE forum_threadpreview ENGINE=InnoDB;
ALTER TABLE forum_threadprofile ENGINE=InnoDB;
ALTER TABLE forum_threadprofile_group ENGINE=InnoDB;
ALTER TABLE forum_threadrush ENGINE=InnoDB;
ALTER TABLE forum_threadtype ENGINE=InnoDB;
ALTER TABLE forum_trade ENGINE=InnoDB;
ALTER TABLE forum_tradecomment ENGINE=InnoDB;
ALTER TABLE forum_tradelog ENGINE=InnoDB;
ALTER TABLE forum_typeoption ENGINE=InnoDB;
ALTER TABLE forum_typeoptionvar ENGINE=InnoDB;
ALTER TABLE forum_typevar ENGINE=InnoDB;
ALTER TABLE forum_warning ENGINE=InnoDB;
ALTER TABLE home_album ENGINE=InnoDB;
ALTER TABLE home_album_category ENGINE=InnoDB;
ALTER TABLE home_appcreditlog ENGINE=InnoDB;
ALTER TABLE home_blacklist ENGINE=InnoDB;
ALTER TABLE home_blog ENGINE=InnoDB;
ALTER TABLE home_blog_category ENGINE=InnoDB;
ALTER TABLE home_blog_moderate ENGINE=InnoDB;
ALTER TABLE home_blogfield ENGINE=InnoDB;
ALTER TABLE home_class ENGINE=InnoDB;
ALTER TABLE home_click ENGINE=InnoDB;
ALTER TABLE home_clickuser ENGINE=InnoDB;
ALTER TABLE home_comment ENGINE=InnoDB;
ALTER TABLE home_comment_moderate ENGINE=InnoDB;
ALTER TABLE home_docomment ENGINE=InnoDB;
ALTER TABLE home_doing ENGINE=InnoDB;
ALTER TABLE home_doing_moderate ENGINE=InnoDB;
ALTER TABLE home_favorite ENGINE=InnoDB;
ALTER TABLE home_feed ENGINE=InnoDB;
ALTER TABLE home_feed_app ENGINE=InnoDB;
ALTER TABLE home_follow ENGINE=InnoDB;
ALTER TABLE home_follow_feed ENGINE=InnoDB;
ALTER TABLE home_follow_feed_archiver ENGINE=InnoDB;
ALTER TABLE home_friend ENGINE=InnoDB;
ALTER TABLE home_friend_request ENGINE=InnoDB;
ALTER TABLE home_friendlog ENGINE=InnoDB;
ALTER TABLE home_notification ENGINE=InnoDB;
ALTER TABLE home_pic ENGINE=InnoDB;
ALTER TABLE home_pic_moderate ENGINE=InnoDB;
ALTER TABLE home_picfield ENGINE=InnoDB;
ALTER TABLE home_poke ENGINE=InnoDB;
ALTER TABLE home_pokearchive ENGINE=InnoDB;
ALTER TABLE home_share ENGINE=InnoDB;
ALTER TABLE home_share_moderate ENGINE=InnoDB;
ALTER TABLE home_show ENGINE=InnoDB;
ALTER TABLE home_specialuser ENGINE=InnoDB;
ALTER TABLE home_userapp ENGINE=InnoDB;
ALTER TABLE home_userappfield ENGINE=InnoDB;
ALTER TABLE home_visitor ENGINE=InnoDB;
ALTER TABLE mobile_setting ENGINE=InnoDB;
ALTER TABLE mobile_wechat_authcode ENGINE=InnoDB;
ALTER TABLE mobile_wechat_masssend ENGINE=InnoDB;
ALTER TABLE mobile_wechat_resource ENGINE=InnoDB;
ALTER TABLE mobile_wsq_threadlist ENGINE=InnoDB;
ALTER TABLE portal_article_content ENGINE=InnoDB;
ALTER TABLE portal_article_count ENGINE=InnoDB;
ALTER TABLE portal_article_moderate ENGINE=InnoDB;
ALTER TABLE portal_article_related ENGINE=InnoDB;
ALTER TABLE portal_article_title ENGINE=InnoDB;
ALTER TABLE portal_article_trash ENGINE=InnoDB;
ALTER TABLE portal_attachment ENGINE=InnoDB;
ALTER TABLE portal_category ENGINE=InnoDB;
ALTER TABLE portal_category_permission ENGINE=InnoDB;
ALTER TABLE portal_comment ENGINE=InnoDB;
ALTER TABLE portal_comment_moderate ENGINE=InnoDB;
ALTER TABLE portal_rsscache ENGINE=InnoDB;
ALTER TABLE portal_topic ENGINE=InnoDB;
ALTER TABLE portal_topic_pic ENGINE=InnoDB;
ALTER TABLE security_evilpost ENGINE=InnoDB;
ALTER TABLE security_eviluser ENGINE=InnoDB;
ALTER TABLE security_failedlog ENGINE=InnoDB;
ALTER TABLE ucenter_admins ENGINE=InnoDB;
ALTER TABLE ucenter_applications ENGINE=InnoDB;
ALTER TABLE ucenter_badwords ENGINE=InnoDB;
ALTER TABLE ucenter_domains ENGINE=InnoDB;
ALTER TABLE ucenter_failedlogins ENGINE=InnoDB;
ALTER TABLE ucenter_feeds ENGINE=InnoDB;
ALTER TABLE ucenter_friends ENGINE=InnoDB;
ALTER TABLE ucenter_mailqueue ENGINE=InnoDB;
ALTER TABLE ucenter_memberfields ENGINE=InnoDB;
ALTER TABLE ucenter_members ENGINE=InnoDB;
ALTER TABLE ucenter_mergemembers ENGINE=InnoDB;
ALTER TABLE ucenter_newpm ENGINE=InnoDB;
ALTER TABLE ucenter_notelist ENGINE=InnoDB;
ALTER TABLE ucenter_pm_indexes ENGINE=InnoDB;
ALTER TABLE ucenter_pm_lists ENGINE=InnoDB;
ALTER TABLE ucenter_pm_members ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_0 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_1 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_2 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_3 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_4 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_5 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_6 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_7 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_8 ENGINE=InnoDB;
ALTER TABLE ucenter_pm_messages_9 ENGINE=InnoDB;
ALTER TABLE ucenter_protectedmembers ENGINE=InnoDB;
ALTER TABLE ucenter_settings ENGINE=InnoDB;
ALTER TABLE ucenter_sqlcache ENGINE=InnoDB;
ALTER TABLE ucenter_tags ENGINE=InnoDB;
ALTER TABLE ucenter_vars ENGINE=InnoDB;

执行过程中，下面注释掉的两张表报错

1
2
3
4
-- ALTER TABLE forum_post ENGINE=InnoDB;
-- ALTER TABLE common_member_grouppm ENGINE=InnoDB;

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key	0.000 sec

建表脚本，如果 ENGINE=MyISAM 则正常，改为 InnoDB 则报错

为了验证错误信息，特意将建表 sql 拿到临时数据库去执行，得到同样的错误提示，这个提示是不准确的。在 MySQL Workbench 下操作得到正确的提示，请继续往下看

1
2
3
4
5
6
7
8
9
CREATE TABLE `common_member_grouppm_temp` (
    `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `gpmid` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
    `status` tinyint(1) NOT NULL DEFAULT '0',
    `dateline` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`uid`,`gpmid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

用 MySQL Workbench 操作，得到解决，大赞 MySQL Workbench

于是用 MySQL Workbench 查看表结构，点击自增字段之后，得到一个重要的提示

1
Only the first key column of a InnoDB table can be AUTO_INCREMENT. Please reorder the columns before making this column AUTO_INCREMENT.

解决方案，重要！

于是得到解决方案，将自增列调整至第一行，同时修改表存储引擎为 InnoDB，即可成功。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- ALTER TABLE common_member_grouppm ENGINE=InnoDB; 改为以下脚本（Workbench 生成的）
ALTER TABLE `dz`.`common_member_grouppm` 
ENGINE = InnoDB ,
CHANGE COLUMN `gpmid` `gpmid` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`gpmid`, `uid`);

-- ALTER TABLE forum_post ENGINE=InnoDB; 改为以下脚本
ALTER TABLE `dz`.`forum_post` 
ENGINE = InnoDB ,
CHANGE COLUMN `position` `position` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`position`, `tid`);

结论：结合使用 MySQL 的经验，进一步体验到，MySQL 的错误提示其实挺不准的，这时个人经验就显得尤其重要了，同时也体现了 DBA 的重要性。

查看数据库存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show variables like '%storage_engine%';
+----------------------------------+-----------------------+
| Variable_name                    | Value                 |
+----------------------------------+-----------------------+
| default_storage_engine           | InnoDB                |
| default_tmp_storage_engine       | InnoDB                |
| disabled_storage_engines         | myisam,memory,archive |
| internal_tmp_disk_storage_engine | InnoDB                |
+----------------------------------+-----------------------+
4 rows in set (0.01 sec)

查看表的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show create table ucenter_tags;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                              |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ucenter_tags | CREATE TABLE `ucenter_tags` (
  `tagname` char(20) NOT NULL,
  `appid` smallint(6) unsigned NOT NULL DEFAULT '0',
  `data` mediumtext,
  `expiration` int(10) unsigned NOT NULL,
  KEY `tagname` (`tagname`,`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 存储引擎参考

MySQL修改数据表存储引擎的3种方法介绍