Linux（docker）中 mysql 表名大小写区分

柔光的暖阳◎ 2022-06-18 05:25 334阅读 0赞

**问题：**  
在docker中使用mysql，通过sql文件导入数据库的时候，创建的表名都是大写字母，而程序中都是用的小写，碰到了下面这种错误：

mysql> select * from task_schedule_job;
    ERROR 1146 (42S02): Table 'ucenter.task_schedule_job' doesn't exist

在网上查到Linux系统中mysql区分大小写

mysql> show variables like '%low%';
    \+----------------------------+--------------------------------------+
    | Variable_name              | Value                                |
    +----------------------------+--------------------------------------+
    | log_slow_admin_statements | OFF                                  |
    | log_slow_slave_statements | OFF                                  |
    | low_priority_updates | OFF                                  |
    | lower_case_file_system | OFF                                  |
    | lower_case_table_names | 0                                    |
    | max_allowed_packet | 4194304                              |
    | slave_allow_batching | OFF                                  |
    | slave_max_allowed_packet | 1073741824                           |
    | slow_launch_time | 2                                    |
    | slow_query_log | OFF                                  |
    | slow_query_log_file | /var/lib/mysql/6a38e59ef95d-slow.log |
    | transaction_allow_batching | OFF                                  |
    +----------------------------+--------------------------------------+
    12 rows in set (0.01 sec)

*其中 lower\_case\_table\_names 的值是 0*  
*0 代表区分大小写*  
*1 代表不区分大小写*  
*（在windows中1 表示不区分大小写 2表示区分大小写）*

**解决：**  
在配置文件的\[mysqld\]后添加lower\_case\_table\_names=1  
Linux中的配置文件应该是在 /etc/mysql/my.cnf

我用的mysql的官方docker镜像，在 /etc/mysql/mysql.conf.d/mysqld.cnf

root@6a38e59ef95d:/etc/mysql/mysql.conf.d# cat mysqld.cnf 
    ...
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    ...

mysql镜像用的Debian 系统，没有安装vi、vim，安装vim

root@6a38e59ef95d:/etc/mysql/mysql.conf.d# cat /proc/version
    Linux version 4.4.52-boot2docker (root@ed11f485244a) (gcc version 4.9.2 (Debian 4.9.2-10) ) #1 SMP Wed Mar 1 23:41:46 UTC 2017
    
    root@6a38e59ef95d:/etc/mysql/mysql.conf.d# apt-get update
    ...
    
    root@6a38e59ef95d:/etc/mysql/mysql.conf.d# apt-get install vim
    ...

修改保存之后重启mysql服务，不过关闭了mysql容器也跟着关闭了如下：

root@6a38e59ef95d:/etc/mysql/mysql.conf.d# service mysql restart
    [info] Stopping MySQL Community Server 5.7.17.
    
    docker@default:~$ docker ps -a
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                      PORTS               NAMES
    6a38e59ef95d        mysql               "docker-entrypoint..."   2 hours ago         Exited (0) 44 seconds ago                       mysql5.7
    9e78e89bdd4a        ubuntu              "/bin/bash"              13 hours ago        Exited (0) 13 hours ago                         kickass_banach

启动容器，进入容器

docker@default:~$ docker start mysql5.7
    mysql5.7
    docker@default:~$ docker ps -a
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                    PORTS                     NAMES
    6a38e59ef95d        mysql               "docker-entrypoint..."   2 hours ago         Up 3 seconds              0.0.0.0:53306->3306/tcp   mysql5.7
    9e78e89bdd4a        ubuntu              "/bin/bash"              13 hours ago        Exited (0) 13 hours ago                             kickass_banach
    docker@default:~$ docker exec -it mysql5.7 /bin/bash
    root@6a38e59ef95d:/# mysql -uroot -p
    Enter password:

这时lower\_case\_table\_names 的值 已经改成了1

mysql> show variables like '%low%';
    \+----------------------------+--------------------------------------+
    | Variable_name              | Value                                |
    +----------------------------+--------------------------------------+
    | log_slow_admin_statements | OFF                                  |
    | log_slow_slave_statements | OFF                                  |
    | low_priority_updates | OFF                                  |
    | lower_case_file_system | OFF                                  |
    | lower_case_table_names | 1                                    |
    | max_allowed_packet | 4194304                              |
    | slave_allow_batching | OFF                                  |
    | slave_max_allowed_packet | 1073741824                           |
    | slow_launch_time | 2                                    |
    | slow_query_log | OFF                                  |
    | slow_query_log_file | /var/lib/mysql/6a38e59ef95d-slow.log |
    | transaction_allow_batching | OFF                                  |
    +----------------------------+--------------------------------------+
    12 rows in set (0.01 sec)

但是还是有问题

mysql> select * from task_schedule_job ;
    ERROR 1146 (42S02): Table 'ucenter.task_schedule_job' doesn't exist

删除之前的数据库,出现ERROR 1010 (HY000)错误

mysql> drop database ucenter;
    ERROR 1010 (HY000): Error dropping database (can't rmdir './ucenter', errno: 39)
    mysql> select @@datadir; +-----------------+
    | @@datadir | +-----------------+
    | /var/lib/mysql/ | +-----------------+
    1 row in set (0.00 sec)

直接查询数据文件目录，删除数据文件

root@6a38e59ef95d:/# cd /var/lib/mysql
    root@6a38e59ef95d:/var/lib/mysql# rm -r -f ucenter

重新建立数据库，执行sql文件，导入的表名都变成了小写  
**参考：**  
[http://linzr33.blog.163.com/blog/static/5738028201482953256877/][http_linzr33.blog.163.com_blog_static_5738028201482953256877]

[http_linzr33.blog.163.com_blog_static_5738028201482953256877]: http://linzr33.blog.163.com/blog/static/5738028201482953256877/