MySQL 操作表中数据

MySQL子查询的简单改写优化

子查询如递归函数一样，有时侯能达到事半功倍的效果，但是其执行效率较低。与表连接相比，子查询比较灵活，方便，形式多样，适合作为查询的筛选条件，而表连接更适合查看多表的数据。

一般情况下，子查询会产生笛卡儿积，表连接的效率要高于子查询。因此在编写 SQL 语句时应尽量使用连接查询。

我们在《MySQL子查询》一节介绍表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。下面我们介绍哪些子查询的查询命令可以改写为表连接。

在检查那些倾向于编写成子查询的查询语句时，可以考虑将子查询替换为表连接，看看连接的效率是不是比子查询更好些。同样，如果某条使用子查询的 SELECT 语句需要花费很长时间才能执行完毕，那么可以尝试把它改写为表连接，看看执行效果是否有所改善。

下面讨论具体该如何做。

1. 改写用来查询匹配值的子查询

下面这条示例语句包含一个子查询，它会把 score 表里的考试成绩查询出来：

SELECT * FROM score  
WHERE grade_id IN (SELECT id FROM grade WHERE category = 'Java');

在编写以上语句时，可以不使用子查询，而是把它转换为一个简单的连接：

SELECT score.* FROM score INNER JOIN grade  
ON score.grade_id = grade.id WHERE grade.category = 'Java';

再来看另一个示例。下面这条查询语句可以把所有女生的考试成绩查询出来：

SELECT * from score  
WHERE student_id IN (SELECT student_id FROM student WHERE sex = 'F') ;

这条语句可以转换为以下连接：

SELECT score.* FROM score INNER JOIN student  
ON score.student_id = student.student_id WHERE student.sex = 'F' ;

我们可以发现这些子查询语句都遵从这样一种形式：

SELECT * FROM table1  
WHERE column1 IN (SELECT column2a FROM table2 WHERE column2b = value);

其中，column1 代表 table1 中的字段，column2a 和 column2b 代表 table2 表中的字段。这类查询都可以被转换为下面这种形式的连接查询：

SELECT table1. * FROM table1 INNER JOIN table2  
ON table1. column1 = table. column2a WHERE table2. column2b = value;

在某些场合，子查询和关联查询可能会返回不同的结果。比如，当 table2 包含 column2a 的多个实例时，就会发生这种情况。这种形式的子查询只会为每个 column2a 值生成一个实例，而连接操作会为所有值生成实例，并且其输出会包含重复行。如果想要防止这种重复记录出现，就要在编写连接查询语句时使用 SELECT DISTINCT，而不能使用 SELECT。

2. 改写用来查询非匹配（缺失）值的子查询

另一种常见的子查询语句类型是：把存在于某个表里，但在另一个表里并不存在的那些值查找出来。“哪些值不存在”有关的问题通常都可以用 LEFT JOIN 来解决。

如下语句用来测试哪些学生没有出现在 absence 表里（用于查找全勤学生）：

SELECT * FROM student  
WHERE student_id NOT IN (SELECT student_id FROM absence) ;

以上查询语句可以使用 LEFT JOIN 来改写：

SELECT student.* FROM student LEFT JOIN absence  
ON student.student_id = absence.student_id WHERE absence.student_ id IS NULL;

通常情况下，如果子查询语句符合如下所示的形式：

SELECT * FROM table1  
WHERE column1 NOT IN ( SELECT column2 FROM table2) ;

那么可以把它改写为下面这样的连接查询：

SELECT table1.* FROM table1 LEFT JOIN table2  
ON table1.column1 = table2.column2 WHERE table2.column2 IS NULL;

这里需要假设 table2.column2 被定义成了 NOT NULL 的。

与 LEFT JOIN 相比，子查询更加直观。大部分人都可以毫无困难地理解“没被包含在...里面”的含义，因为它不是数据库编程技术带来的新概念。而“左连接”有所不同，很难用自然语言直观地描述出它的含义。

SQL查询语句先执行SELECT？兄弟你认真的么？

学到这里，我们已经写了无数个 SQL 查询了，但是还是很难确切的说出它的执行顺序。

下面看看 SELECT 语句的完整语法，如下：

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

其执行顺序如下：

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <JOIN,LEFT JOIN,RIGHT JOIN...>
<JOIN表> # 指定JOIN，用于添加数据到ON之后的虚表中，例如LEFT JOIN会将左表的剩余数据添加到虚表中
WHERE
<WHERE条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于HAVING子句进行判断，在书写上这类聚合函数是写在HAVING判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在GROUP BY子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

引擎在执行上述每一步时，都会在内存中形成一张虚拟表，然后对虚拟表进行后续操作，并释放没用的虚拟表的内存，以此类推。

下面我们具体介绍一下 SQL 查询语句的执行顺序（下面“VT”表示虚拟表）

1. FROM：SELECT * FROM table_1, table_2; 与 SELECT * FROM table_1 JOIN table_2; 的结果一致，都是表示求笛卡尔积；用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有 SELECT 语句最先执行的操作，其他操作时在这个表上进行的，也就是 FROM 操作所完成的内容
2. ON：从 VT1 表中筛选符合条件的数据，形成 VT2 表
3. JOIN：将该 JOIN 类型的数据补充到 VT2 表中，例如 LEFT JOIN 会将左表的剩余数据添加到虚表 VT2 中，形成 VT3 表；若表的数量大于 2，则会重复 1-3 步
4. WHERE：执行筛选，（不能使用聚合函数）得到 VT4 表
5. GROUP BY：对 VT4 表进行分组，得到 VT5 表；其后处理的语句，如 SELECT、HAVING，所用到的列必须包含在 GROUP BY 条件中，没有出现的需要用聚合函数
6. HAVING：筛选分组后的数据，得到 VT6 表
7. SELECT：返回列得到 VT7 表
8. DISTINCT：用于去重得到 VT8 表
9. ORDER BY：用于排序得到 VT9 表
10. LIMIT：返回需要的行数，得到 VT10

需要注意的是：

• GROUP BY 条件中，每个列必须是有效列，不能是聚合函数
• NULL 值也会作为一个分组返回
• 除了聚合函数，SELECT 子句中的列必须在 GROUP BY 条件中

根据以上内容，我们可以回答以下经常遇到的问题：

• 可以在 GROUP BY 之后使用 WHERE 吗？不行，GROUP BY 是在 WHERE 之后。
• 可以基于 GROUP BY 里的东西进行 ORDER BY 吗？可以，ORDER BY 基本上是在最后执行的，所以可以基于任何东西进行 ORDER BY。
• LIMIT 是在什么时候执行？在最后

但是在实际应用中，数据库不一定会按照 JOIN、WHERE、GROUP BY 的顺序来执行查询。因为它们会进行一系列优化，在不改变查询结果的前提下，把执行顺序打乱，从而让查询执行得更快。

比如：

SELECT * FROM score sc LEFT JOIN student stu 
ON sc.student_id = stu.id WHERE s.name = 'C语言中文网'

以上语句只需要找出名字叫“C语言中文网”的学生信息，那就没必要对两张表的所有数据执行左连接。在连接之前系统先进行过滤，这样查询会快得多，而且对于这个查询来说，先执行过滤并不会改变查询结果。

MySQL如何多表关联更新？

MySQL 可以基于多表查询更新数据。对于多表的 UPDATE 操作需要慎重，建议在更新前，先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。

下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 price；另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。

操作前先分别查看两张表的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM product;
+----+-----------+-----------------------+-------+----------+
| id | productid | productname           | price | isdelete |
+----+-----------+-----------------------+-------+----------+
|  1 |      1001 | C语言中文网Java教程   |   100 |        0 |
|  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 |
|  3 |      1003 | C语言中文网Python教程 |   120 |        0 |
|  4 |      1004 | C语言中文网C语言教程  |   150 |        0 |
|  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 |
+----+-----------+-----------------------+-------+----------+
5 rows in set (0.02 sec)
mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |  NULL |
|  2 |      1002 |  NULL |
|  3 |      1003 |  NULL |
|  4 |      1004 |  NULL |
|  5 |      1005 |  NULL |
+----+-----------+-------+
5 rows in set (0.01 sec)

下面是 MySQL 多表更新在实践中的几种不同写法。执行不同的 SQL 语句，仔细观察 SQL 语句执行后表中数据的变化，很容易就能理解多表联合更新的用法。

1. 使用UPDATE

在 MySQL 中，可以使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新，SQL 语句和运行结果如下：

mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

2. 通过 INNER JOIN

另外一种方法是使用 INNER JOIN 来多表更新。SQL 语句如下：

mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
Query OK, 5 rows affected (0.09 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

3. 通过 LEFT JOIN

也可以使用 LEFT JOIN 来做多表更新，如果 product_price 表中没有产品价格记录的话，将 product 表的 isdelete 字段设置为 1。在 product 表添加 1006 商品，且不在 product_price 表中添加对应信息，SQL 语句如下。

mysql> UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM product;
+----+-----------+-----------------------+-------+----------+
| id | productid | productname           | price | isdelete |
+----+-----------+-----------------------+-------+----------+
|  1 |      1001 | C语言中文网Java教程   |   100 |        0 |
|  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 |
|  3 |      1003 | C语言中文网Python教程 |   120 |        0 |
|  4 |      1004 | C语言中文网C语言教程  |   150 |        0 |
|  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 |
|  6 |      1006 | C语言中文网Spring教程 |  NULL |        1 |
+----+-----------+-----------------------+-------+----------+
6 rows in set (0.00 sec)

4. 通过子查询

也可以通过子查询进行多表更新，SQL 语句和执行过程如下：

mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

另外，上面的几个例子都是在两张表之间做关联，只更新一张表中的记录。MySQL 也可以同时更新两张表，如下语句就同时修改了两个表。

UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8, p.dateUpdate = CURDATE()

两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 dateUpdate 两个字段。

日常开发中，一般都是用单表 UPDATE 语句，很少写多表关联的 UPDATE。

MySQL如何处理无效数据值？

MySQL处理数据的基本原则是“垃圾进来，垃圾出去”，通俗一点说就是你传给 MySQL 什么样的数据，它就会存储什么样的数据。如果在存储数据时没有对它们进行验证，那么在把它们检索出来时得到的就不一定是你所期望的内容。
 
有几种 SQL 模式可以在遇到“非正常”值时抛出错误，如果你对其他数据库管理系统比较熟悉，会发现这种行为和其他的数据库管理系统很像。
 
下面介绍 MySQL 默认情况下如何处理非正常数据和启用各种 SQL 模式时会对数据处理产生哪些影响。
 
默认情况下，MySQL 会按照以下规则来处理越界（即超出取值范围）的值和其他非正常值：

• 对于数值列或 TIME 列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点，并把结果值存储起来。
• 对于除 TIME 列以外的其他类型列，非法值会被转换成与该类型一致的“零”值。
• 对于字符串列（不包括 ENUM 或 SET），过长的字符串将被截断到该列的最大长度。
• 给 ENUM 或 SET 类型列进行赋值时，需要根据列定义里给出的合法取值列表进行。如果把不是枚举成员的值赋给 ENUM 列，那么列的值就会变成空字符串。如果把包含非集合成员的子字符串的值赋给 SET 列，那么这些字符串会被清理，剩余的成员才会被赋值给列。

如果在执行增删改查等语句时发生了上述转换，那么 MySQL 会给出警告消息。在执行完其中的某一条语句之后，可以使用 SHOW WARNINGS 语句来查看警告消息的内容。
 
如果需要在插入或更新数据时执行更严格的检查，那么可以启用以下两种 SQL 模式中的一种：

SET sql_mode = 'STRICT_ALL_TABLES' ;  
SET sql_mode = 'STRICT_TRANS_TABLES';

对于支持事务的表，这两种模式都是一样的。如果发现某个值无效或缺失，那么会产生一个错误，并且语句会中止执行，并进行回滚，就像什么事都没发生过一样。
 
对于不支持事务的表，这两种模式有以下效果。

1. 对于这两种模式，如果在插入或修改第一个行时，发现某个值无效或缺失，那么结果会产生一个错误，语句会中止执行，就像什么事都未发生过一样。 这跟事务表的行为很相似。
2. 在用于插入或修改多个行的语句里，如果在第一行之后的某个行出现了错误，那么会出现某些行被修改的情况。这两种模式决定着，这条语句此时此刻是要停止执行，还是要继续执行。

• 在 STRICT_ALL_TABLES 模式下，会抛出一个错误，并且语句会停止执行。因为受该语句影响的许多行都已被修改，所以这将会导致“部分更新”问题。
• 在 STRICT_TRANS_TABLES 模式下，对于非事务表，MySQL 会中止语句的执行。只有这样做，才能达到事务表那样的效果。只有当第一行发生错误时，才能达到这样的效果。如果错误在后面的某个行上，那么就会出现某些行被修改的情况。由于对于非事务表，那些修改是无法撤销的，因此 MySQL 会继续执行该语句，以避免出现“部分更新”的问题。它会把所有的无效值转换为与其最接近的合法值。对于缺失的值，MySQL 会把该列设置成其数据类型的隐式默认值，

通过以下模式可以对输入的数据进行更加严格的检查：

• ERROR_ FOR_ DIVISION_ BY_ ZERO：在严格模式下，如果遇到以零为除数的情况，它会阻止数值进入数据库。如果不在严格模式下，则会产生一条警告消息，并插入 NULL。
• NO_ ZERO_ DATE：在严格模式下，它会阻止“零”日期值进入数据库。
• NO_ ZERO_ IN_ DATE：在严格模式下，它会阻止月或日部分为零的不完整日期值进入数据库。

简单来说，MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度、类型等。比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下 MySQL 不会报错。如果定义了 char 或 varchar 类型的字段，当写入或更新的数据超过了定义的长度也不会报错。
 
虽然我们会在代码中做数据校验，但一般认为非严格模式对于编程来说没有任何好处。MySQL开启严格模式从一定程序上来讲也是对我们代码的一种测试，如果我们没有开启严格模式并且在开发过程中也没有遇到错误，那么在上线或代码移植的时候将有可能出现不兼容的情况，因此在开发过程做最好开启 MySQL 的严格模式。
 
可通过SELECT @@sql_mode;命令查看当前是严格模式还是非严格模式。
 
例如，如果想让所有的存储引擎启用严格模式，并对“被零除”错误进行检查，那么可以像下面这样设置 SQL 模式：

SET sql_mode 'STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

如果想启用严格模式，以及所有的附加限制，那么最为简单的办法是启用 TRADITIONAL 模式：

SET sql_ mode 'TRADITIONAL' ;

TRADITIONAL 模式的含义是“启用严格模式，当向 MySQL 数据库插入数据时，进行数据的严格校验，保证错误数据不能插入。用于事务表时，会进行事务的回滚”。
 
可以选择性地在某些方面弱化严格模式。如果启用了 SQL 的 ALLOW_ INVALID_ DATES 模式，那么MySQL将不会对日期部分做全面检查。相反，它只会要求月份值在 1~12 之间，而天数处于 1~31 之间，即允许像‘2000-02-30’或‘2000-06-31’这样的无效值。
 
另一个制止错误的办法是在 INSERT 或 UPDATE 语句里使用 IGNORE 关键字。这样那些会因无效值而导致错误的语句，将只会导致警告的出现。这些选项能让你灵活地为你的应用选择正确的有效性检查级别。

向MySQL发送一个请求的时候，MySQL到底做了些什么？

通过《MySQL内部架构》一节的介绍，大家对 MySQL 的整体架构已经有了一定的了解，本节我们主要介绍数据库收到请求后的具体工作流程。下面是一张简单的数据库执行流程图：

下面从数据库架构的角度介绍数据库的工作流程：

1. 连接层

1）连接处理：客户端同数据库服务层通过连接管理模块建立 TCP 连接，并请求一个连接线程。如果连接池中有空闲的连接线程，则分配给这个连接，如果没有，在没有超过最大连接数的情况下，创建新的连接线程负责这个客户端。

连接管理模块负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接收客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的缓存等。

2）授权认证：在真正的操作之前，还需要调用用户模块进行授权检查，来验证用户是否有权限。通过后，连接线程开始接收并处理来自客户端的请求。

用户模块所实现的功能，主要包括用户的登录连接权限控制和用户的授权管理。它就像 MySQL 的大门守卫一样，决定是否给来访者“开门”。

在 MySQL 中，将客户端请求分为了两种类型：一种是 query（SQL语句），需要调用 Parser（查询解析器）才能够执行的请求；一种是 command（命令），不需要调用 Parser 就可以直接执行的请求。

2. SQL层

连接线程接收到 SQL 语句之后，将语句交给 Parser 进行语法分析和语义分析。之后根据类型的不同，有些会直接处理，有些会分发给其他模块来处理。

如果是一个 query 类型的请求，会将控制权交给 Query 解析器。

Query 解析器首先分析是不是一个 Select 类型的 query。是则调用查询缓存模块，让它检查该 query 在 Query Cache（查询缓存）中是否已经存在，如果有结果可以直接返回给客户端。没有结果则将控制权交给 Optimizer（查询优化器），进行查询的优化。

如果是表变更语句，则分别交给 Insert 处理器、Delete 处理器、Update 处理器、Create 处理器，以及 Alter 处理器这些小模块来负责。

3. 存储引擎层

在各个模块收到 Query 解析或其它模块分发过来的请求后，首先会通过访问控制模块检查连接用户是否有访问目标表以及目标字段的权限，如果有，就会调用表管理模块请求相应的表，并获取对应的锁。

表变更管理模块主要是负责完成一些 DML 和 DDL 的 query，如：update，delete，insert，create table，alter table 等语句的处理。

当表变更管理模块“获取”打开的表之后，就会根据该表的相关信息，判断表的存储引擎类型和其他相关信息。根据表的存储引擎类型，提交请求给存储引擎接口模块，调用对应的存储引擎实现模块，进行相应处理。

不过，对于表变更管理模块来说，可见的仅是存储引擎接口模块所提供的一系列“标准”接口，底层存储引擎实现模块的具体实现，对于表变更管理模块来说是透明的。他只需要调用对应的接口，并指明表类型，之后接口模块会根据表类型调用正确的存储引擎来进行相应的处理。

当一条 query 或者一个 command 处理完成（成功或者失败）之后，控制权都会交还给连接线程模块。

如果处理成功，则将处理结果（可能是一个 ResultSet，也可能是成功或者失败的标识）通过连接线程反馈给客户端。

如果处理过程中发生错误，也会将相应的错误信息发送给客户端，然后连接线程模块会进行相应的清理工作，并继续等待后面的请求。之后重复上面的过程，或者与客户端断开连接，最后关闭连接，释放连接线程。

不得不说的SQL注入攻击，别一不留神就被利用了！

SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。

简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。

SQL 注入已经成为互联网世界 Web 应用程序的最大风险，我们有必要从开发、测试、上线等各个环节对其进行防范。下面介绍 SQL 注入的原理及避免 SQL 注入的一些方法。

SQL注入的原理

SQL 注入的原理主要有以下 4 点：

1）恶意拼接查询

我们知道，SQL 语句可以查询、插入、更新和删除数据，且使用分号来分隔不同的命令。例如：

SELECT * FROM users WHERE user_id = $user_id

其中，user_id 是传入的参数，如果传入的参数值为“1234; DELETE FROM users”，那么最终的查询语句会变为：

SELECT * FROM users WHERE user_id = 1234; DELETE FROM users

如果以上语句执行，则会删除 users 表中的所有数据。

2）利用注释执行非法命令。

SQL 语句中可以插入注释。例如：

SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=$version

如果 version 包含了恶意的字符串'-1' OR 3 AND SLEEP(500)--，那么最终查询语句会变为：

SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version='-1' OR 3 AND SLEEP(500)--

以上恶意查询只是想耗尽系统资源，SLEEP(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令，那么将会造成更大的破坏。

3）传入非法参数

SQL 语句中传入的字符串参数是用单引号引起来的，如果字符串本身包含单引号而没有被处理，那么可能会篡改原本 SQL 语句的作用。 例如：

SELECT * FROM user_name WHERE user_name = $user_name

如果 user_name 传入参数值为 G'chen，那么最终的查询语句会变为：

SELECT * FROM user_name WHERE user_name ='G'chen'

一般情况下，以上语句会执行出错，这样的语句风险比较小。虽然没有语法错误，但可能会恶意产生 SQL 语句，并且以一种你不期望的方式运行。

4）添加额外条件

在 SQL 语句中添加一些额外条件，以此来改变执行行为。条件一般为真值表达式。例如：

UPDATE users SET userpass='$userpass' WHERE user_id=$user_id;

如果 user_id 被传入恶意的字符串“1234 OR TRUE”，那么最终的 SQL 语句会变为：

UPDATE users SET userpass= '123456' WHERE user_id=1234 OR TRUE;

这将更改所有用户的密码。

避免SQL注入

对于 SQL 注入，我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。

1. 过滤输入内容，校验字符串

过滤输入内容就是在数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤，还可以使用正则表达式匹配安全的字符串。

如果值属于特定的类型或有具体的格式，那么在拼接 SQL 语句之前就要进行校验，验证其有效性。比如对于某个传入的值，如果可以确定是整型，则要判断它是否为整型，在浏览器端（客户端）和服务器端都需要进行验证。

2. 参数化查询

参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。

MySQL 的参数格式是以“?”字符加上参数名称而成，如下所示：

UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行。因此就算参数中含有破坏性的指令，也不会被数据库所运行。

3. 安全测试、安全审计

除了开发规范，还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。通过多个环节的检查，一般是可以避免 SQL 注入的。

有些人认为存储过程可以避免 SQL 注入，存储过程在传统行业里用得比较多，对于权限的控制是有一定用处的，但如果存储过程用到了动态查询，拼接 SQL，一样会存在安全隐患。

下面是在开发过程中可以避免 SQL 注入的一些方法

1. 避免使用动态SQL

避免将用户的输入数据直接放入 SQL 语句中，最好使用准备好的语句和参数化查询，这样更安全。

2. 不要将敏感数据保留在纯文本中

加密存储在数据库中的私有/机密数据，这样可以提供了另一级保护，以防攻击者成功地排出敏感数据。

3. 限制数据库权限和特权

将数据库用户的功能设置为最低要求；这将限制攻击者在设法获取访问权限时可以执行的操作。

4. 避免直接向用户显示数据库错误

攻击者可以使用这些错误消息来获取有关数据库的信息。

一些编程框架对于写出更安全的代码也有一定的帮助，因为它提供了一些处理字符串的函数和使用查询参数的方法。但同样，你仍然可以编写出不安全的 SQL 语句。所以归根到底，我们需要有良好的编码规范，并能充分利用参数化查询、字符串处理和参数校验等多种办法来保护数据库和程序的安全。

说明：SQL 注入技术不是单凭一篇文章就可以讲完的，这里只带领大家掌握 SQL 注入的原理及常见的几种防止 SQL 注入的方法。