MySQL view column name capitalization

Question

I'm in the process of upgrading our in-house applications from MySQL 5.0.45 to 5.1.41. In the old environment we had mirrored some tables using symlinks from one DB to another. For a few reasons, current versions of MySQL block that completely.

What seemed like the best replacement was using a view instead, but I'm having a problem. A few old tables have column names that are capitalized. However some of our application code (PHP) does a SELECT with the capitalized name and also some SELECTs with lower case column names. Which normally works fine because MySQL returns tables with a column name capitalized as you referenced it in the SELECT. However, with a view that doesn't seem to be the case. See the following:

create table t(A int);
Query OK, 0 rows affected (0.18 sec)

> create view v as select A from t;
Query OK, 0 rows affected (0.00 sec)

> insert into t values(47);
Query OK, 1 row affected (0.01 sec)

> select a from t;
+------+
| a    |
+------+
|   47 |
+------+
1 row in set (0.00 sec)

> select a from v;
+------+
| A    |
+------+
|   47 |
+------+
1 row in set (0.00 sec)

Notice that the capitalization of the column name returned in the SELECT queries is different whether or not you select from the table or the view. The table returns the capitalization as specified in your query at runtime; the view returns the capitalization when the view was created. This seems to be consistent through both versions of MySQL, both the command-line client and through the PHP library client.

One trick I discovered is that if you add a GROUP BY to the SELECT in the view it will use the capitalization in your query at runtime. Unfortunately that breaks updating through the view, which I need.

Is there any way to make the column name capitalization match the query at runtime, which doesn't involve going back and changing all of our application code?

Prize

There's a couple server settings you could try: dev.mysql.com/doc/refman/5.0/en/… – Zing

Anyway I don't really see the problem. As you are using the data it returns, not the table name. But if needed why not use something like SELECT a AS fiElD_NaMe FROM v; – Kaliope

Marc: unfortunately those apply to database and table names, not column names. – Prize

Jules: The problem is that programs expect to receive results with the capitalization used in the select. If the view had the column named field_name and you select fiElD_NaMe and your PHP code looks for fiElD_NaMe (case sensitive), it would not find anything because the results would be returned under field_name. – Prize

If you change to a case-insensitive encoding, this problem will probably be gone. – Bitterling

Answer 1

I have to say I'm not 100 % sure, but I strongly suspect you can't get a matching case in your views without modifying the application code. Have a look at how the view is defined (I'm using MySQL 5.1.56):

mysql> show create view v;
+------+-----------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                           | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v    | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`A` AS `A` from `t` | utf8                 | utf8_general_ci      |
+------+-----------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

As you can see, MySQL adds a column alias (AS), and AFAIK there's no way to make it behave differently. Defining a column name explicitly has the same result:

mysql> create view v2 (viewa) as select A from t;
Query OK, 0 rows affected (0.02 sec)

mysql> select Viewa from v2;
+-------+
| viewa |
+-------+
|    47 |
+-------+
1 row in set (0.00 sec)

mysql> show create view v2;
+------+----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t`.`A` AS `viewa` from `t` | utf8                 | utf8_general_ci      |
+------+----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Adding a (somewhat funny-looking) column alias in all your SQL queries would obviously fix the issue:

mysql> select a as a from v;
+------+
| a    |
+------+
|   47 |
+------+
1 row in set (0.00 sec)

Santasantacruz

Yeah that's essentially the same conclusion I came to. I ended up going back and extending the DB model to allow us to include a table into a group of identical tables that are mirrored across logical DBs. Then when data are written to one table it also writes to all the other tables in the group. A little like the binlog except it's across logical DBs and not physical servers. Not my preferred solution but it's been working for a while now without any problems. – Prize

Answer 2

instead of,

    mysql> select a from v;

use this (with an alias),

    mysql> select a as a from v;
    +------+
    | a    |
    +------+
    |   47 |
    +------+
    1 row in set (0.00 sec)

Agnes

Answer 3

you must be use Mysql column alias like this: select a as a from v;

Gopherwood

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Squalid