我在这里分配了"test123";到变量@searchstring。我希望显示列的名称是变量的值(即,在本例中test123(。
这是我在MySQL控制台中发布的:
SET @searchstring="test123"; #SET the searchstring you want to search
SELECT DISTINCT(caselist.casenumber1) AS "@searchstring" FROM caselist LIMIT 1;
这是我在屏幕上得到的回报:
+---------------+
| @searchstring |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
期望的结果是:
+---------------+
| test123 |
+---------------+
| 0 |
+---------------+
我实际发出的查询将更加复杂,并且多次引用@searchstring。@searchstring的内容会更改,具体取决于我要搜索的内容。这就是为什么我决定提前为它赋值一次。当我想搜索其他东西时,我只需要提前更改一次变量。
问题:如何使用列标题来显示变量@searchstring(即本例中的test123(的内容,而不是变量的实际名称
谢谢。
如果我这样做(即删除AS后变量名周围的引号(:
SET @searchstring="test123"; #SET the searchstring you want to search
SELECT DISTINCT(caselist.casenumber1) AS @searchstring FROM caselist ;
然后我得到这个错误消息:
mysql> SELECT DISTINCT(caselist.casenumber1) AS @searchstring FROM caselist ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@searchstring FROM caselist' at line 1
mysql>
一个选项是使用动态查询来解决这个问题:
SET @searchstring = "test123"; #SET the searchstring you want to search
SET @sql = CONCAT('SELECT DISTINCT casenumber1 AS ',
@searchstring,
' FROM caselist LIMIT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请在此处查看演示。