MySQL 如何将变量传递给MySQL脚本

您可以使用会话变量将变量传递给MySQL脚本。首先，您需要使用SET命令设置会话变量。然后您需要将该变量传递给MySQL脚本。

语法如下 –

第一步 ：使用Set命令。

SET @anyVariableName = 'yourValue';

第二步 ：将变量传递给MySQL脚本。

UPDATE yourTableName SET yourColumnName1 = yourColumnName1+integerValue WHERE yourColumnName2 = @anyVariableName;

为了理解上述语法，让我们创建一个表。创建表的查询如下 –

mysql> create table Employee_Information
  -> (
  -> EmployeeId int NOT NULL AUTO_INCREMENT,
  -> EmployeeName varchar(20) NOT NULL,
  -> EmployeeSalary int,
  -> EmployeeStatus varchar(20),
  -> PRIMARY KEY(EmployeeId)
  -> );
Query OK, 0 rows affected (0.53 sec)

现在，您可以使用insert命令在表中插入一些记录。查询如下 –

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Sam',17650,'FullTime');
Query OK, 1 row affected (0.13 sec)

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Carol',12000,'Trainee');
Query OK, 1 row affected (0.18 sec)

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Bob',17650,'FullTime');
Query OK, 1 row affected (0.20 sec)

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Mike',12000,'Trainee');
Query OK, 1 row affected (0.14 sec)

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('John',17650,'FullTime');
Query OK, 1 row affected (0.16 sec)

使用SELECT语句显示表中的所有记录。

mysql> select *from Employee_Information;

以下是输出 –

+------------+--------------+----------------+----------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeStatus |
+------------+--------------+----------------+----------------+
|          1 | Sam          |          17650 | FullTime       |
|          2 | Carol        |          12000 | Trainee        |
|          3 | Bob          |          17650 | FullTime       |
|          4 | Mike         |          12000 | Trainee        |
|          5 | John         |          17650 | FullTime       |
+------------+--------------+----------------+----------------+
5 rows in set (0.00 sec)

以下是将变量传递给MySQL脚本的查询 –

mysql> set @EmpStatus = 'FullTime';
Query OK, 0 rows affected (0.03 sec)

mysql> update Employee_Information set EmployeeSalary = EmployeeSalary+6500 where EmployeeStatus = @EmpStatus;
Query OK, 3 rows affected (0.18 sec)
Rows matched - 3 Changed - 3 Warnings - 0

现在再次使用SELECT语句检查表记录。我将FullTime员工的EmployeeSalary增加了6500。

查询如下 –

mysql> select *from Employee_Information;

以下是输出结果 –

+------------+--------------+----------------+----------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeStatus |
+------------+--------------+----------------+----------------+
|          1 | Sam          |          24150 | FullTime       |
|          2 | Carol        |          12000 | Trainee        |
|          3 | Bob          |          24150 | FullTime       |
|          4 | Mike         |          12000 | Trainee        |
|          5 | John         |          24150 | FullTime       |
+------------+--------------+----------------+----------------+
5 rows in set (0.00 sec)

阅读更多：MySQL 教程