Geek Answers Handbook

MySQL sees column name

I am currently updating our own applications from MySQL 5.0.45 to 5.1.41. In the old environment, we mirrored some tables using symbolic links from one database to another. For several reasons, current versions of MySQL block this completely.

It seemed like the best replacement is using the view, but I have a problem. Several old tables have column names that are capitalized. However, some of our applications (PHP) do SELECT with a capitalized name, as well as some SELECT with lowercase names. This usually works fine, because MySQL returns tables with a column name that is capitalized as you specified it in SELECT. However, from the point of view, it seems not so. 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)

, , SELECT, , . , ; . , MySQL, , PHP.

, , , GROUP BY SELECT , . , , .

, ?

+5
2

, 100%, , . , ( 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)

, MySQL (AS), AFAIK -. :

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)

( ) SQL- :

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

<

    mysql> select a from v;

( ),

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

More articles:


All Articles