MySQL表称为DB.Table而不是Table？

Question

我在 MySQL 中遇到了一个奇怪的问题。我创建了一个新表，然后我无法对其进行任何更改。该软件查找名为“TED.Students”(TED 是数据库名称)而不是“student”的表。代码如下:

mysql> CREATE DATABASE TED;
Query OK, 1 row affected (0.00 sec)
mysql> USE TED;
Database changed
mysql> CREATE TABLE students
    -> (
    -> StudentID int,
    -> FirstName varchar(255),
    -> LastName varchar(255)
    -> );
Query OK, 0 rows affected (0.57 sec)
mysql> INSERT INTO Students
    -> VALUES (1, Dvir, Sade);
ERROR 1146 (42S02): Table 'TED.Students' doesn't exist
mysql> SHOW TABLES;
+---------------+
| Tables_in_TED |
+---------------+
| students      |
+---------------+
1 row in set (0.01 sec)
mysql> CREATE TABLE students ( StudentID int, FirstName varchar(255 ) , LastName varchar(255) );
ERROR 1050 (42S01): Table 'students' already exists

我在网上搜索但找不到问题的解释。也许我错过了一些非常简单的东西，但我不知道是什么。直到现在我还没有遇到这个问题。唯一改变的是我从 Windows 迁移到 Linux (Ubuntu)，但为什么会有所不同呢？

Answer 1

您不知道区分大小写:

INSERT INTO students
    VALUES (1, Dvir, Sade);

应该可以工作。请注意学生和学生之间的区别

查看手册Identifier Case Sensitivity

Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. (Emphasis by me) This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix.

By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:

mysql> 从 tbl_name 中选择 col_name AS a -> WHERE a.col_name = 1 或 A.col_name = 2;

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.