6 messages in com.mysql.lists.mysqlRe: RENAME TABLE with CONCAT string f...| From | Sent On | Attachments |
|---|---|---|
| Jason Dixon | 28 May 2005 05:12 | |
| Paul DuBois | 28 May 2005 05:51 | |
| Jason Dixon | 28 May 2005 08:00 | |
| Paul DuBois | 28 May 2005 08:29 | |
| mfat...@free.fr | 28 May 2005 08:49 | |
| Paul DuBois | 28 May 2005 09:13 |
| Subject: | Re: RENAME TABLE with CONCAT string fails |
|---|---|
| From: | Paul DuBois (pa...@mysql.com) |
| Date: | 05/28/2005 09:13:01 AM |
| List: | com.mysql.lists.mysql |
At 17:50 +0200 5/28/05, mfat...@free.fr wrote:
Hi, As Paul said, since concat gives a string, you can use this fact in preparing statement (v4.1). This works fine for me :
Ah, yes. This'll work. I forgot about prepared statements. :-)
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT("flows_", replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';'); select @tt;
mysql> show tables like 'flow%'; +-------------------------+ | Tables_in_world (flow%) | +-------------------------+ | flows_2005_05_27 | +-------------------------+ 1 row in set (0.00 sec)
mysql> mysql> set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT("flows_", replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql> prepare stmt from @tt; Query OK, 0 rows affected (0.00 sec) Statement prepared
mysql> execute stmt ; mysql> deallocate prepare stmt;
mysql> show tables like 'flow%'; +-------------------------+ | Tables_in_world (flow%) | +-------------------------+ | flows_2005_05_26 | +-------------------------+ 1 row in set (0.00 sec)
Mathias
Selon Paul DuBois <pa...@mysql.com>:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your SQL statement, then execute the statement.
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mfat...@free.fr
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com