3

test.sql:

SET @my_var = 50;

CREATE TABLE test_table (
    id SMALLINT NOT NULL,
    my_text VARCHAR(@my_var),
    PRIMARY KEY (id)
);

Command line interaction:

mysql> source d:/test.sql;
Query OK, 0 rows affected (0.00 sec)

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
'@my_var),
    PRIMARY KEY (id)
)' at line 3

Is there a way to do this?

Hammerite
  • 21,755
  • 6
  • 70
  • 91
  • Why do you want to do this? VARCHAR is an efficient storage mechanism. You can just put in a large number, big enough for any reasonable data, and you incur no additional storage overhead. – Gordon Linoff Aug 19 '12 at 22:57
  • 1
    Gordon, your point is a reasonable one, but my question is also valid irrespective of whether I justify the specific example I gave. I choose not to be sidetracked by your question. – Hammerite Aug 19 '12 at 23:00
  • Gordon's point kind of invalidates the question, actually. Creating tables at runtime, especially with variable properties, is a "SQL smell". The fact that you're asking it indicates that there is something fundamentally wrong with your design, and that you should take steps to fix it. –  Aug 19 '12 at 23:58
  • duskwuff, it really doesn't. The question is one of "is there a way to do this", not "is it a good idea to do this". I realise that requests for ways to do unusual things can sometimes be symptomatic of a misguided approach to the underlying problem, but the question qua question is a valid one. – Hammerite Aug 20 '12 at 00:40

1 Answers1

2

I can't think of a way other than compiling the whole CREATE TABLE as a string and executing it:

SET @my_var = 50;

PREPARE sql FROM '
CREATE TABLE test_table (
    id SMALLINT NOT NULL,
    my_text VARCHAR(' + @my_var + '),
    PRIMARY KEY (id)
);
'
EXECUTE sql;
DEALLOCATE PREPARE sql;

Its pretty ugly, but should work.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129