MySQL: How to pass parameters from command line?

Question

In PostgreSQL you can pass in named parameters from the command line when running queries with psql. For example, if you run a command like this:

psql -v v1=12 -f query.sql

Inside query.sql you can reference v1 like so:

select * from table_1 where id = :v1;

Does MySQL support anything similar?

Did you try reading the docs first?: http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html — Nick, Aug 02 '16 at 16:01
Yes, I didn't see anything relating to this. If it's not supported in MySQL I was hoping someone can confirm that, as I'm new to it. — pca2, Aug 02 '16 at 16:12
I didn't see anything either, but check to see if either of these help: http://stackoverflow.com/questions/20145367/pass-mysql-variables-to-script-from-command-line or http://stackoverflow.com/questions/10229324/pass-parameter-to-mysql-script-command-line — Nick, Aug 02 '16 at 16:24

score 0 · Answer 1 · answered Jun 26 '19 at 23:32

Yes.
TLDR; Using the example from your question:

SET @v1=12;
SELECT * FROM table_1 WHERE id = @v1;

Note that they are destroyed at the end of the current session.

DETAILS

User Defined Variables in MySQL start with an @.

Here is one way to set a variable:

SET @mysite := "example.com";

You can also use MySQL's CONCAT when setting variables:

SET @home_url := CONCAT("https://www.", @mysite);
SET @site_url := CONCAT(@home_url, "/wordpress");

To see their values:
SELECT @mysite, @home_url, @site_url;

+-------------+-------------------------+-----------------------------------+
| @mysite     | @home_url               | @site_url                         |
+-------------+-------------------------+-----------------------------------+
| example.com | https://www.example.com | https://www.example.com/wordpress |
+-------------+-------------------------+-----------------------------------+
1 row in set (0.00 sec)

To update some values:

UPDATE wp_options SET option_value = @home_url WHERE option_name = "home";
UPDATE wp_options SET option_value = @site_url WHERE option_name = "siteurl";

Unfortunately, there does not seem to be a way to have MySQL show you a list of all user defined variables. For example SHOWVARIABLES LIKE "%my%" will only show (system?) variables , but no user defined variables.

However if you are running MariaDB 10.2 (equivalent to MySQL 5.7) or later, MariaDB provides a plugin that creates a table of user variables, so you can query that.
See my answer here for details.

OP asked how to "pass in named parameters from the command line": basically, does `mysql` provide a command-line switch like `psql`? Saying that `mysql` provides user-defined variables inside on the SQL-level itself does not answer the question. — Stefan van den Akker, Feb 08 '20 at 14:46
@StefanvandenAkker As far as I can tell, I gave the equivalent of the command he was looking for, given his example, in the top lines of my answer, then went on to give more details, and point out limitations. I do not understand how this "didn't address the question at all". Please point out my misunderstanding. — SherylHohman, Feb 08 '20 at 17:07

score -2 · Answer 2 · answered Aug 02 '16 at 17:59

A stored procedure example.

DROP PROCEDURE IF EXISTS so_gibberish.blahBlah7;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE so_gibberish.blahBlah7
(
)
BEGIN
    select CONCAT('The variable is ',@myVar) AS 'theAnswer';
END$$
DELIMITER ;

Test it:

OS Prompt> mysql -uroot -p so_gibberish -e "set @myVar='Hank Aaron'; call blahBlah7();"
Enter password: ********
+----------------------------+
| theAnswer                  |
+----------------------------+
| The variable is Hank Aaron |
+----------------------------+

so_gibberish is the database name. -e means run this query.

@myVar becomes a connection-based User Variable.

OP asked how to "pass in named parameters from the command line": basically, does mysql provide a command-line switch like psql? Saying that mysql provides user-defined variables inside on the SQL-level itself does not answer the question. — Stefan van den Akker, Feb 08 '20 at 14:47

MySQL: How to pass parameters from command line?

2 Answers2