MSSQL DBA技术咨询：MySQL脚本中是否支持类似TSQL的变量定义与全脚本复用

Hey there! As someone who’s bounced between MSSQL and MySQL DBA work, I totally get why you want to reuse variables across your scripts—clean, maintainable code matters everywhere. The good news is MySQL absolutely supports variable reuse, but there are key differences from T-SQL that tripped up your initial script. Let’s break this down step by step.

First: Fix Your Variable Assignment Syntax #

Your original script uses backticks (`) around string values, which is a common gotcha for MSSQL folks. In MySQL, backticks are only for escaping identifiers (like table names with spaces or reserved words), not string literals. For variable values, use single quotes ('):

-- Correct variable assignment
SET @newuser := 'usr652@%.domain.com';
SET @pw := 'ge?jJ!ARxbz$qiC6n';

Note: := is the standard assignment operator for MySQL variables (works in both SET and SELECT statements). You can also use = in SET statements, but := avoids ambiguity in complex queries.

The Big Difference: DDL Can’t Directly Use Variables #

Unlike MSSQL, MySQL doesn’t let you use user variables (those starting with @) directly in DDL statements like CREATE USER, ALTER USER, or GRANT. This is because MySQL parses DDL syntax before resolving variables. To work around this, you need dynamic SQL with prepared statements—build your SQL string dynamically, then execute it.

Full Corrected Script #

Here’s how to adjust your script to work properly:

-- Define reusable session variables
SET @newuser := 'usr652@%.domain.com';
SET @pw := 'ge?jJ!ARxbz$qiC6n';

-- 1. Create the user (dynamic SQL required)
SET @create_sql := CONCAT('CREATE USER ''', @newuser, ''' IDENTIFIED BY ''', @pw, '''');
PREPARE stmt FROM @create_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. Mark password as expired
SET @alter_sql := CONCAT('ALTER USER IF EXISTS ''', @newuser, ''' PASSWORD EXPIRE');
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Grant USAGE privilege
SET @grant_usage_sql := CONCAT('GRANT USAGE ON `POP%`.* TO ''', @newuser, '''');
PREPARE stmt FROM @grant_usage_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Grant SELECT privilege
SET @grant_select_sql := CONCAT('GRANT SELECT ON `POP%`.* TO ''', @newuser, '''');
PREPARE stmt FROM @grant_select_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Show user grants
SET @show_grants_sql := CONCAT('SHOW GRANTS FOR ''', @newuser, '''');
PREPARE stmt FROM @show_grants_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Key Notes on This Approach: #

• Escaping Single Quotes: When building dynamic SQL, we use '' (two single quotes) to escape the single quotes needed around the username/password in the final statement. If your password had single quotes, you’d use REPLACE(@pw, '''', '''''') to escape them properly.
• Clean Up Prepared Statements: Always use DEALLOCATE PREPARE to free resources after execution.
• Variable Scope: User variables (@var) are session-scoped—they persist for your MySQL connection but vanish when you disconnect.

Alternative: Local Variables for Stored Programs #

If you’re working in a stored procedure/function, you can use DECLARE to define local variables (no @ prefix) scoped to the BEGIN...END block—similar to T-SQL:

DELIMITER //
CREATE PROCEDURE CreatePopUser(IN username VARCHAR(100), IN userpass VARCHAR(255))
BEGIN
  -- Local variable (only exists inside this procedure)
  DECLARE user_host VARCHAR(100);
  SET user_host := CONCAT(username, '@%.domain.com');
  
  -- Dynamic SQL still required for DDL
  SET @create_sql := CONCAT('CREATE USER ''', user_host, ''' IDENTIFIED BY ''', userpass, '''');
  PREPARE stmt FROM @create_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Wrap-Up #

While MySQL’s variable handling for DDL is more verbose than MSSQL’s, it’s fully possible to reuse variables across scripts. The core takeaway is using dynamic prepared statements for DDL that references variables, and sticking to single quotes for string literals instead of backticks.

内容的提问来源于stack exchange，提问作者Pete