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?
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.
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.
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.
