Can I declare a variable in the mysql command line

Question

lets say I wanted to do $table = 'datatables_demo'; so then I could do select * from $table? How/Can I do this?

LOG BELOW

mysql> select * from datatables_demo;                                           +----+------------+-----------+-------------+--------+------------+--------+
| id | first_name | last_name | position    | office | start_date | salary |
+----+------------+-----------+-------------+--------+------------+--------+
|  1 | Tiger      | Nixon     | Accountant  | Tokyo  | 2016-11-08 | 320800 |
|  2 | Garrett    | Winters   | Accountant2 | Tokyo  | 2016-11-08 | 170750 |
|  3 | Ashton     | Cox       | Accountant3 | Tokyo  | 2016-11-08 |  86000 |
|  4 | Cedric     | Kelly     | Accountant4 | Tokyo  | 2016-11-08 | 433060 |
|  5 | Tiger5     | Nixon     | Accountant  | Tokyo  | 2016-11-08 | 320800 |
+----+------------+-----------+-------------+--------+------------+--------+
5 rows in set (0.00 sec)

mysql> $table = 'datatables_demo';
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 '$table = 'datatables_demo'' at line 1
mysql> table = 'datatables_demo';
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 'table = 'datatables_demo'' at line 1
mysql> var table varchar2(20)
    -> ;

Possible duplicate of [How to declare a variable in MySQL?](http://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql) — takendarkk, Nov 10 '16 at 00:58
that dupe won't help solve this issue. You need a stored proc and a concat and a prepared stmt in mysql. All in all, a lot of horseplay for not much ROI — Drew, Nov 10 '16 at 01:05

score 1 · Answer 1 · edited Nov 10 '16 at 02:04

1

you should use PREPARE STATEMENT to implement dynamic SQL statements.

show you an example

delimiter // 
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
    SET @s = CONCAT('SELECT ',col,' FROM ',tbl );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
delimiter ;

edited Nov 10 '16 at 02:04

Rahul

76,197
13
71
125

answered Nov 10 '16 at 01:46

xpisme

19
6

Why char? those input parameter should be varchar instead – Rahul Nov 10 '16 at 02:05
tks how do I apply that to my specific example `select * from ; `? – HattrickNZ Nov 10 '16 at 02:31

Can I declare a variable in the mysql command line

1 Answers1