由于某些原因,当我try 添加这一个表aux
时,它总是将@@@
附加到information_schema.innodb_sys_tables
中的表名.如果我用aux2
代替,它工作得很好.为什么?我的意思见下文.
mysql> select * from information_schema.innodb_sys_tables where name like '%aux%' and name like 'tsm%';
+----------+------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+------------+------+--------+-------+-------------+------------+---------------+
| 67408 | tsm/aux2 | 1 | 54 | 67394 | Antelope | Compact | 0 |
| 67407 | tsm/aux@@@ | 1 | 54 | 67393 | Antelope | Compact | 0 |
+----------+------------+------+--------+-------+-------------+------------+---------------+
我有两个脚本,除了表名之外都一样.
这是一个不起作用的方法:
CREATE TABLE `aux` (
`AUXKEY` int(11) NOT NULL AUTO_INCREMENT,
`AUXSTM` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSTS` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`auxuid` varchar(50) COLLATE latin1_general_cs NOT NULL,
`AUXSKU` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`auxskd` varchar(120) COLLATE latin1_general_cs NOT NULL,
`AUXQTY` int(11) NOT NULL,
`AUXEQT` int(11) NOT NULL,
`AUXTAG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXETG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSEQ` int(11) NOT NULL,
`AUXZNE` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXFLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXOID` varchar(26) COLLATE latin1_general_cs DEFAULT NULL,
`AUXLNN` int(11) NOT NULL,
`AUXLLN` int(11) NOT NULL,
`AUXLST` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`AUXGRP` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTFP` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTYP` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXUOM` int(11) NOT NULL,
`AUXLOT` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCLS` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTSK` int(11) NOT NULL,
`AUXITK` int(11) NOT NULL,
`AUXSUB` int(11) NOT NULL,
`AUXSCT` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSDTR` datetime NOT NULL,
`AUXLNK` int(11) NOT NULL,
`AUXTSZ` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPRY` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXWAV` varchar(2) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCPO` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXDID` varchar(26) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCAS` int(11) NOT NULL,
`AUXRTG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCRT` int(11) NOT NULL,
`AUXRDN` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXUM1` int(11) NOT NULL,
`AUXPPS` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPSQ` int(11) NOT NULL,
`AUXTLR` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXMTG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXLCD` varchar(12) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSHDR` datetime NOT NULL,
`AUXRVF` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXDNO` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`auxfid` varchar(3) COLLATE latin1_general_cs NOT NULL,
UNIQUE KEY `AUXKEY` (`AUXKEY`),
KEY `AUX_AUXLSTK` (`AUXLST`,`AUXLCD`,`AUXKEY`),
KEY `AUX_AUXOID` (`AUXOID`),
KEY `AUX_AUXSTSK` (`AUXSTS`,`AUXKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
这是一个确实有效的方法:
CREATE TABLE `aux2` (
`AUXKEY` int(11) NOT NULL AUTO_INCREMENT,
`AUXSTM` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSTS` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`auxuid` varchar(50) COLLATE latin1_general_cs NOT NULL,
`AUXSKU` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`auxskd` varchar(120) COLLATE latin1_general_cs NOT NULL,
`AUXQTY` int(11) NOT NULL,
`AUXEQT` int(11) NOT NULL,
`AUXTAG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXETG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSEQ` int(11) NOT NULL,
`AUXZNE` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXFLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXOID` varchar(26) COLLATE latin1_general_cs DEFAULT NULL,
`AUXLNN` int(11) NOT NULL,
`AUXLLN` int(11) NOT NULL,
`AUXLST` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`AUXGRP` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTFP` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTYP` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXUOM` int(11) NOT NULL,
`AUXLOT` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCLS` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXTSK` int(11) NOT NULL,
`AUXITK` int(11) NOT NULL,
`AUXSUB` int(11) NOT NULL,
`AUXSCT` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSDTR` datetime NOT NULL,
`AUXLNK` int(11) NOT NULL,
`AUXTSZ` varchar(4) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPRY` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXWAV` varchar(2) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCPO` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSLC` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
`AUXDID` varchar(26) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCAS` int(11) NOT NULL,
`AUXRTG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXCRT` int(11) NOT NULL,
`AUXRDN` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXUM1` int(11) NOT NULL,
`AUXPPS` varchar(22) COLLATE latin1_general_cs DEFAULT NULL,
`AUXPSQ` int(11) NOT NULL,
`AUXTLR` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXMTG` varchar(6) COLLATE latin1_general_cs DEFAULT NULL,
`AUXLCD` varchar(12) COLLATE latin1_general_cs DEFAULT NULL,
`AUXSHDR` datetime NOT NULL,
`AUXRVF` varchar(1) COLLATE latin1_general_cs DEFAULT NULL,
`AUXDNO` varchar(8) COLLATE latin1_general_cs DEFAULT NULL,
`auxfid` varchar(3) COLLATE latin1_general_cs NOT NULL,
UNIQUE KEY `AUXKEY` (`AUXKEY`),
KEY `AUX_AUXLSTK` (`AUXLST`,`AUXLCD`,`AUXKEY`),
KEY `AUX_AUXOID` (`AUXOID`),
KEY `AUX_AUXSTSK` (`AUXSTS`,`AUXKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
aux
是预订的还是怎么的?我在保留词列表中看不到它,但这是我唯一能想到的事情.即使跑alter table aux2 rename aux;
也会增加这一点,所以aux
在某种程度上是有意义的.
MySQL版本: 在Amazon RDS上,我使用的是5.7.42-log 在CentOS 6.5 dev服务器上,我使用的是5.6.51-log
两台机器上的行为相同.
顺便说一句,我判断的第一个地方是MySQL保留字,我没有看到它.我只提到"保留字",因为我抓住了救命稻草.
我从命令行做一切. 差不多mysql .... < aux2.sql
个. 这是我可以用来创建表格的方法. 我不能使用其他工具,因为有时我转储数据库来创建重复的副本,这就是我发现问题的原因.