How to add column with default value from a variable in MySQL?

Question

I've tried

SET @branch := 'BRANCH_A';

ALTER TABLE item
ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT @branch FIRST;

This returns error:

SQL Error (1064): 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 '@branch FIRST' at line 2

This is working fine

ALTER TABLE item
ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT 'BRANCH_A' FIRST;

Where did I go wrong?

You didn't go wrong, You cannot default a column to variable - unless you use dynamic sql.Is there some reason you want to do something like this? — P.Salmon, Jun 12 '19 at 11:19
@P.Salmon I'm making a snippets because we need to export, alter, and reupload tables when things go wrong (this happens quite often because of bad internet connection in our client shop). There will be many line, not just ADD COLUMN branch, so I think that using variable at the top is tidier than replacing the value in the middle. It is just a temporary quick fix while we are working on a program to do this automagically. — Christhofer Natalius, Jun 12 '19 at 11:25
not sure whats going wrong with a bad internet connection but still, maybe you can erborate a more by explaining the user case? it is a running (web) application which could store information? — Raymond Nijland, Jun 12 '19 at 11:31
if a webapplication is the case you could make the web application a offline application with html 5 cache and use WebSQL or IndexedDB to store the infromation local when the internet connection goes down.. HTML 5 has API's to detect that when the internet is back online push the information from the local database to the server.. But offcource the better option would be to get better internet if that possible over there. — Raymond Nijland, Jun 12 '19 at 11:33
@RaymondNijland The client has several branch running a local desktop program, and then the program upload the item stock to online server so that other branch can see item stock in each branch. But if the connection is bad, of course the items stock will become obsolete. And when the queue become too many, we need to upload it manually to server. — Christhofer Natalius, Jun 13 '19 at 02:14
The old program uploads per item, which is too slow. I'm working on making it uploads batch of items instead, but I don't know how to make post request sending array of objects in c#, I've asked question about it here but still no answer. https://stackoverflow.com/questions/56342631 — Christhofer Natalius, Jun 13 '19 at 02:14

score 1 · Accepted Answer · answered Oct 25 '21 at 14:41

You need to use dynamic sql in order to use a variable in your query:

SET @branch := 'BRANCH_A';
SET @query := CONCAT('ALTER TABLE item ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT "', @branch, '" FIRST');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;

How to add column with default value from a variable in MySQL?

1 Answers1