欢迎访问昆山宝鼎软件有限公司网站! 设为首页 | 网站地图 | XML | RSS订阅 | 宝鼎邮箱 | 后台管理


新闻资讯

MENU

软件开发知识
原文出处: 潇湘隐者

在MySQL傍边,有大概碰着表名巨细写敏感的问题。其实这个跟平台(操纵系统)有关,也跟系统变量lower_case_table_names有干系。下面总结一下,有乐趣可以查察官方文档“Identifier Case Sensitivity”

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.8.1, “MySQL Extensions to Standard SQL”. Thelower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

在 MySQL 中, 数据库对应于数据目次中的目次。数据库中的每个表对应于数据库目次中至少一个文件 (大概更多, 详细取决于存储引擎)。触发器也对应于文件。因此, 底层操纵系统的区分巨细写在数据库、表和触发器名称的巨细写敏感度方面起着重要浸染。这意味着这些名称在 Windows 中不区分巨细写, 但在大大都范例的 Unix 中都是区分巨细写的。一个显著的破例是 macOS, 它是基于 Unix 的, 但利用的是不区分巨细写的默认文件系统范例 (HFS+)。可是, macOS 还支持 UFS 卷, 它们与任何 Unix 一样都是区分巨细写的。拜见1.8.1 节, “MySQL Extensions to Standard SQL“。lower_case_table_names 系统变量还影响处事器处理惩罚标识符巨细写敏捷度的方法, 如本节后头所述。

 Linux系统:

  • 数据库名与表名是严格区分巨细写的;
  • 表的别名是严格区分巨细写的;
  • 列名与列的别名在所有的环境下均是忽略巨细写的;
  • 变量名也是严格区分巨细写的;
  •   Windows系统:

  • 都不区分巨细写
  • Mac OS下(非UFS卷):
  • 都不区分巨细写 
  • 留意事项:列名、索引、存储进程、事件名称在任何平台上都不区分巨细写,列别名也不区分巨细写。

    Notice:Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

    下面在测试情况为Red Hat Enterprise Linux Server release 5.7, MySQL 5.6.20: 

    mysql> show variables like 'lower_case_table_names';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 0     |
    +------------------------+-------+
    1 row in set (0.00 sec)
     
    mysql> 
     
    mysql> use mydb;
    Database changed
    mysql> create table test(id int);
    Query OK, 0 rows affected (0.07 sec)
     
    mysql> create table TEST(id int);
    Query OK, 0 rows affected (0.09 sec)
     
    mysql> insert into test values(1);
    Query OK, 1 row affected (0.03 sec)
     
    mysql> insert into TEST value(2);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> select * from test;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
     
    mysql> select * from TEST;
    +------+
    | id   |
    +------+
    |    2 |
    +------+
    1 row in set (0.00 sec)
     
    mysql>

    在设置文件my.cnf中配置lower_case_table_names=1后(1暗示不区分巨细写,0暗示区分巨细写),重启MySQL处过后,举办如下测试:

    mysql> use mydb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
     
    Database changed
    mysql> select * from test;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
     
    mysql> select * from TEST;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
     
    mysql>

    可以制止这样  <a href=苏州软件公司 的问题呈现" class="aligncenter size-full wp-image-29539" title="73542-20180815225124293-1996659401" src="/uploads/allimg/c180821/1534O5454BG0-192b.png" />

    可以看到此时不管是test、TEST抑或Test,昆山软件开发,都是会见的test,此时不能会见”TEST”表了,系统变量lower_case_table_names是只读变量,昆山软件开发,也无法在当前会话修改,这种配置下,假如存在沟通的表名的话,利用mysqldump备份数据库时会碰着下面错误:

    mysqldump: Got error: 1066: Not unique table/alias: ‘test’ when using LOCK TABLES

    碰着这种环境就较量贫苦了,必需在设置文件my.cnf中配置变量lower_case_table_names=0,重启MySQL处事,所以提前筹划,利用统一的定名法则就很是重要,可以制止这样的问题呈现。别的系统变量lower_case_table_names有三个值:别离是0、1、2. 

    1. 配置成0:表名按你写的SQL巨细写存储,大写就大写小写就小写,较量时巨细写敏感。 

    2. 配置成1:表名转小写后存储到硬盘,较量时巨细写不敏感。  

    3. 配置成2:表名按你写的SQL巨细写存储,大写就大写小写就小写,较量时统一转小写较量。 

    Value

    Meaning

    0

    Table and database names are stored on disk using the lettercase specified in theCREATE TABLE or CREATE DATABASEstatement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS). If you force this variable to 0 with–lower-case-table-names=0 on a case-insensitive file system and access MyISAMtablenames using different lettercases, index corruption may result.

    1

    Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

    2

    Table and database names are stored on disk using the lettercase specified in theCREATE TABLE or CREATE DATABASEstatement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as forlower_case_table_names=1. 


    关于数据库名巨细写敏感,会碰着下面问题:

    1:ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./xxxx’, errno: 39)

    1:ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxx', errno: 39)
     
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | MyDB               |
    | mydb               |
    | mysql              |
    | performance_schema |
    | tmonitor           |
    | xiangrun           |
    +--------------------+
    7 rows in set (0.01 sec)
    mysql> show variables like 'lower_case_table_names';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 1     |
    +------------------------+-------+
    1 row in set (0.00 sec)
     
    mysql> drop database mydb;
    ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)
    mysql>

    办理要领:在设置文件my.cnf中配置变量lower_case_table_names=0,昆山软件开发,重启MySQL处事,然后就可以drop 掉数据库了。 

    2: ERROR 1049 (42000): Unknown database ‘xxx’

    mysql> show variables like 'lower_case_table_names';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 1     |
    +------------------------+-------+
    1 row in set (0.01 sec)
     
    mysql> 
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | MyDB               |
    | mysql              |
    | performance_schema |
    | tmonitor           |
    | xiangrun           |
    +--------------------+
    6 rows in set (0.01 sec)
     
    mysql> use MyDB;
    ERROR 1049 (42000): Unknown database 'mydb'
    mysql>

    参考资料:

  • https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html