mysql - 为什么我无法在 MySQL 中创建表？

Question

我们之前有一个 MySQL 数据库。其中，我们有一个名为 trace 的表。我们早已删除了整个数据库并创建了另一个同名的数据库。现在，我们尝试从备份脚本重新创建表trace，并得到表已存在。即使新数据库中显然没有表。如果我尝试创建以前存在的表，则会收到该错误。如果我创建一个从未存在过的随机表，那就没问题。

这是我用来创建表的代码:

DROP TABLE IF EXISTS `Trace`;
CREATE TABLE `Trace` (
  `TraceKey` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Trace Key',
  `TableName` varchar(100) NOT NULL DEFAULT '' COMMENT 'Table Name',
  `RecordKey` int(10) NOT NULL,
  `Action` varchar(100) NOT NULL DEFAULT '',
  `ActionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time Stamp',
  `UserName` varchar(100) NOT NULL DEFAULT '' COMMENT 'UserName',
  PRIMARY KEY (`TraceKey`)
) ENGINE=InnoDB AUTO_INCREMENT=6735 DEFAULT CHARSET=latin1;

错误:

Table 'trace' already exists

我们确实有大约 20 个表(trace 是其中之一)，并且有很多外键(如果有帮助的话)。但我们显然删除了数据库并重新创建了它。

更新

为了测试，我尝试了这个并且它有效

CREATE TABLE trace (id INT,data VARCHAR(100));

但是，在删除该表并再次尝试后，如下所示:

CREATE TABLE Trace (id INT,data VARCHAR(100));

它不起作用，我收到跟踪已存在错误。

区别是大写还是小写？

更新2

这绝对是一个大写与小写的问题。将表名称从 Trace 更改为 trace 即使使用旧脚本也能正常工作。

对于为什么会这样有什么想法吗？

Answer 1

您的 MySQL 服务器上的 lower_case_table_names 设置可能有误。可以找到正确的值 In MySQL documentation 。它指出:

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

1. Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

2. Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

   Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

如果您在 Windows 上使用 MySQL 并且有 lower_case_table_names=0 那么您可能会收到此错误，因为表 Trace(区分大小写)在 MySQL 中不存在，但文件 Trace.frm 已存在于文件系统上。