MySQL:大小写敏感性

Question

我有一个非常复杂的问题。开发团队使用jdbc (区分大小写)编写了一个SQL脚本，该脚本在MySQL中搜索表。但是在MySQL中，表是用小写写的，所以它会抛出一个错误。

问题是开发团队不接受将表名更改为小写，而利用漏洞的团队不接受将表名更改为大写。

我知道我们可以使用lower_case_table_names to 1来强迫大小写变得不敏感，但是我没有重新启动mysqld的权限。

我该怎么办？

Answer 1

Create view with Uppercase name 

    mysql> select * from f_val;
    ERROR 1146 (42S02): Table 'TEST.f_val' doesn't exist
    mysql> select * from F_VAL;
    +------+------+------+--------+-------+-------+
    | ID   | F_ID | DATE | I      | II    | III   |
    +------+------+------+--------+-------+-------+
    |    1 |   11 | 1/26 | peanut | where | Now   |
    |    2 |   22 | 1/26 | butter | he    | there |
    |    3 |   11 | 1/27 |  jelly | at    | he    |
    |    4 |   33 | 1/27 |  time  | huh   | go    |
    |    1 |   11 | 1/25 | cake   | where | Now   |
    +------+------+------+--------+-------+-------+
    5 rows in set (0.01 sec)

    CREATE VIEW f_val AS SELECT * FROM F_VAL;

select * from f_val;
+------+------+------+--------+-------+-------+
| ID   | F_ID | DATE | I      | II    | III   |
+------+------+------+--------+-------+-------+
|    1 |   11 | 1/26 | peanut | where | Now   |
|    2 |   22 | 1/26 | butter | he    | there |
|    3 |   11 | 1/27 |  jelly | at    | he    |
|    4 |   33 | 1/27 |  time  | huh   | go    |
|    1 |   11 | 1/25 | cake   | where | Now   |
+------+------+------+--------+-------+-------+
5 rows in set (0.00 sec)

mysql> INSERT INTO f_val(ID,F_ID,DATE) VALUES(10,10,"1/25")
    -> ;
Query OK, 1 row affected (0.08 sec)

select * from f_val;
+------+------+------+--------+-------+-------+
| ID   | F_ID | DATE | I      | II    | III   |
+------+------+------+--------+-------+-------+
|    1 |   11 | 1/26 | peanut | where | Now   |
|    2 |   22 | 1/26 | butter | he    | there |
|    3 |   11 | 1/27 |  jelly | at    | he    |
|    4 |   33 | 1/27 |  time  | huh   | go    |
|    1 |   11 | 1/25 | cake   | where | Now   |
|   10 |   10 | 1/25 | NULL   | NULL  | NULL  |
+------+------+------+--------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM F_VAL;
+------+------+------+--------+-------+-------+
| ID   | F_ID | DATE | I      | II    | III   |
+------+------+------+--------+-------+-------+
|    1 |   11 | 1/26 | peanut | where | Now   |
|    2 |   22 | 1/26 | butter | he    | there |
|    3 |   11 | 1/27 |  jelly | at    | he    |
|    4 |   33 | 1/27 |  time  | huh   | go    |
|    1 |   11 | 1/25 | cake   | where | Now   |
|   10 |   10 | 1/25 | NULL   | NULL  | NULL  |
+------+------+------+--------+-------+-------+
6 rows in set (0.01 sec)