Declare variable in MySQL Workbench

You can define variables within SQL Workbench/J that can be referenced in your SQL statements. This is done through the internal command .

Table of Contents Show

17.2. Populate a variable from a SELECT statement
17.3. Populate a variable from a file
17.5. Using variables in SQL statements
17.6. Prompting for values during execution
17.7. Controlling the order of variables during prompting
How do I declare a variable in MySQL workbench?
How to declare variables in MySQL?
How to declare variable in stored procedure in MySQL Workbench?
Can we declare variable in MySQL query?

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

7 defines a variable with the name

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

8 and the value

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

9. If the variable does not exist, it will be created. If it exists its value will be overwritten with the new value. To remove a variable simply set its value to nothing:

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

0. Alternatevily you can use the command

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

1 to remove a variable definition.

Variable substitution is also done within Macros. If your macro definition contains a reference to a SQL Workbench/J variable, this will be treated the same way as in regular statements.

The definition of variables can also be read from a properties file. This can be done by specifying

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

2 for the

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

6 command, or by passing the

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

4 parameter when starting SQL Workbench/J. Please see the description for the for details.

WbVarDef -file=/temp/myvars.def

This file has to be a standard Java "properties" file. Each variable is listed on a single line in the format

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

5. Lines starting with a

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

6 character are ignored (comments). Assuming the file

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

7 had the following content:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

After executing

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

8 there would be three variables available in the system:

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

9, that could be used e.g. in a SELECT query:

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

SQL Workbench/J would expand the variables and send the following statement to the server:

SELECT * FROM person where name='Dent' or id=42;

17.2. Populate a variable from a SELECT statement

A variable can also be defined as the result of a

SELECT * FROM person where name='Dent' or id=42;

0 statement. The query can be defined through the parameter

SELECT * FROM person where name='Dent' or id=42;

1. Alternatively this can be done by by using

SELECT * FROM person where name='Dent' or id=42;

2 as the first character after the equal sign. The query needs to be enclosed in double quotes.

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

or the old short syntax using

SELECT * FROM person where name='Dent' or id=42;

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

If the

SELECT * FROM person where name='Dent' or id=42;

0 returns more than one column, multiple variables can be defined by specifying a comma separated list of variable names. The following statement will define the variables

SELECT * FROM person where name='Dent' or id=42;

5 and

SELECT * FROM person where name='Dent' or id=42;

6 based on the values returned from the

SELECT * FROM person where name='Dent' or id=42;

0 statement:

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

When executing the statement, SQL Workbench/J only retrieves the first row of the result set. Subsequent rows are ignored. If the select returns more columns than variable names, the additional values are ignored. If more variables are listed than columns are present in the result set, the additional variables will be undefined.

The parameter

SELECT * FROM person where name='Dent' or id=42;

8 can be used to control the result if the value retrieved through the query is

SELECT * FROM person where name='Dent' or id=42;

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

0 - if the value from the query is NULL, the variable be set to an empty string.

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

1 - the value is ignored. An existing variable will not be changed. If the variable does not exist, it will not be created.

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

2 - if the value from the query is NULL, the variable will be removed.

17.3. Populate a variable from a file

A variable can also be defined by reading the content of a file (this is different from reading the variable definition from a file).

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

When executing the statement, SQL Workbench/J will read the content of the file

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

3 and use that as the value for the variable

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

If the file contents contains references to variables, these are replaced after the content as been loaded. To disable replacement, use the parameter

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

Consider the following sequence of statements, where the file

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

6 contains the statement

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

WbVarDef person_id=42;
WbVarDef -variable=my_select -contentFile=select.txt;
$[my_select];

After running the above script, the variable

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

8, will have the value of

WbVarDef -variable=myvar -query="SELECT id FROM person WHERE name='Dent'"

9. When "running"

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

0, the row with id=42 will be retrieved.

To view a list of currently defined variables execute the command

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

1. This will display a list of currently defined variables and their values. You can edit the resulting list similar to editing the result of a

SELECT * FROM person where name='Dent' or id=42;

0 statement. You can add new variables by adding a row to the result, remove existing variables by deleting rows from the result, or edit the value of a variable. If you change the name of a variable, this is the same as removing the old, and creating a new one.

17.5. Using variables in SQL statements

The defined variables can be used by enclosing them in special characters inside the SQL statement. The default is set to

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

3 and

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

4, you can use a variable this way:

SELECT firstname, lastname FROM person WHERE id=$[id_variable];

If you have a variable with the name

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

5 defined, the sequence

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

6 will be replaced with the current value of the variable.

Variables will be replaced after replacing macro .

If the SQL statement requires quotes for the SQL literal, you can either put the quotes into the value of the variable (e.g.

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

7) or you put the quotes around the variable's placeholder, e.g.:

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

Variables will be replaced inside string literals (e.g.

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

9) and comments (e.g.

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

0 or

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

If you are using values in your regular statements that actually need the prefix (

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

3 or suffix

WbVarDef [email protected]"SELECT id FROM person WHERE name='Dent'"

4) characters, please make sure that you have no variables defined. Otherwise you will have unpredictable results. If you want to use variables but need to use the default prefix for marking variables in your statements, you can configure a different prefix and suffix for flagging variables. To change the prefix e.g. to

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

4 and the suffix (i.e end of the variable name) to

SELECT * FROM person where name='$[person_name]' or id=$[var_id];

6, add the following lines to your

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

6 file:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

You may leave the suffix empty, but the prefix definition may not be empty.

17.6. Prompting for values during execution

You can also use variables in a way that SQL Workbench/J will prompt you during execution of a SQL statement that contains a variable.

If you want to be prompted for a value, reference the value with a question mark in front of its name:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

If you execute this statement, SQL Workbench/J will prompt you for the value of the variable

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

7. If the variable is already defined you will see the current value of the variable. If the variable is not yet defined it will be implicitly defined with an empty value.

If you use a variable more then once in your statement it is sufficient to define it once as a prompt variable. Prompting for a variable value is especially useful inside a macro definition.

You can also define a conditional prompt with using an ampersand instead of a question mark. In this case you will only be prompted if no value is assigned for the variable:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

The first time you execute this statement (and no value has been assigned to

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

7 before using

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

9 or on the command line) you will be prompted for a value for

WbVarDef -variable=id,name -query="SELECT id,firstname FROM person WHERE lastname='Dent'"

7. Any subsequent execution of the statement (or any other statement referencing

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

1) will re-use the value you entered.

When defining a variable, you can specify a list of values that should be entered in the dialog.

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

17.7. Controlling the order of variables during prompting

By default the variables shown in the prompt dialog are sorted alphabetically. This behavior can be changed by setting the configuration property

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

2 to true, e.g. using

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

If the property is set to

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

3, the variables are shown in the order they were declared:

#Define the ID that we need later
var_id=42
person_name=Dent
another_variable=24

The dialog to enter the variables will show them in the order

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

WbVarDef -variable=somevar -contentFile=/temp/mydata.txt

The default behaviour can be changed in the "General" section of the . If enabled, each window will have its own set of variables. Setting or changing a variable in one window will not affect variables in the other windows.

How do I declare a variable in MySQL workbench?

In MySQL, we can use the SET statement to declare a variable and also for initialization. After setting the value, it is accessible from anywhere in the script. An user-defined variable always begins with the @ sign.

How to declare variables in MySQL?

We can set some value to the variable with the help of SET command. SET @yourVariableName=value; Note − In the SELECT statement, the “yourVariableName” contains the NULL value and after using the SET command it contains the value which we have given.