MySQL专题10之MySQL序列使用、MySQL处理重复数据、MySQL以及SQL的注入

neversoft

　　1、MySQL序列使用
　　-  MySQL序列是一组整数：1,2,3...,由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
　　-  使用AUTO_INCREMENT：MySQL中最简单使用序列的方法就是使用MySQL AUTO_INCREMENT来定义列。
　　-  以下实例中创建了数据表insect，insect中id无需指定值可以实现自动增长。
mysql> CREATE TABLE insect　　
    -> (
　　
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
　　
    -> PRIMARY KEY (id),
　　
    -> name VARCHAR(30) NOT NULL, # type of insect
　　
    -> date DATE NOT NULL, # date collected
　　
    -> origin VARCHAR(30) NOT NULL # where collected
　　
);
　　
Query OK, 0 rows affected (0.02 sec)
　　
mysql> INSERT INTO insect (id,name,date,origin) VALUES
　　
    -> (NULL,'housefly','2001-09-10','kitchen'),
　　
    -> (NULL,'millipede','2001-09-10','driveway'),
　　
    -> (NULL,'grasshopper','2001-09-10','front yard');
　　
Query OK, 3 rows affected (0.02 sec)
　　
Records: 3  Duplicates: 0  Warnings: 0
　　
mysql> SELECT * FROM insect ORDER BY id;
　　
+----+-------------+------------+------------+
　　
| id | name        | date       | origin     |
　　
+----+-------------+------------+------------+
　　
|  1 | housefly    | 2001-09-10 | kitchen    |
　　
|  2 | millipede   | 2001-09-10 | driveway   |
　　
|  3 | grasshopper | 2001-09-10 | front yard |
　　
+----+-------------+------------+------------+
　　
3 rows in set (0.00 sec)
　　-  获取AUTO_INCREMENT值：在MySQL的客户端中你可以使用SQL中的LAST_INSERT_ID()函数来获取最后的插入表中的自增列的值。
　　-  重置序列：如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排序，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要小心，如果在删除的同时又有 新记录添加，有可能会出现数据混乱。操作如下所示：
mysql> ALTER TABLE insect DROP id;　　
mysql> ALTER TABLE insect
　　
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
　　
    -> ADD PRIMARY KEY (id);
　　-  设置序列的开始值：一般情况下序列的开始值为1，但是如果你需要指定一个开始值100，那么我们可以通过以下语句来实现。
mysql> CREATE TABLE insect　　
    -> (
　　
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
　　
    -> PRIMARY KEY (id),
　　
    -> name VARCHAR(30) NOT NULL,
　　
    -> date DATE NOT NULL,
　　
    -> origin VARCHAR(30) NOT NULL
　　
)engine=innodb auto_increment=100
　　-  或者你也可以在表创建成功后，通过以下语句来实现：
mysql> ALTER TABLE t AUTO_INCREMENT = 100;　　2、MySQL处理重复数据
　　-  有些MySQL数据表中可能存在重复的记录，有些情况我们允许重复数据的存在。但是有时候我们也需要删除这些重复的数据。本节将介绍如何防止数据表中出现重复的数据以及如何删除数据表中的重复的数据。
　　a、防止表中出现重复的数据
　　-  你可以在MySQL数据表中设置指定的字段为PRIMARY KEY（主键）或者UNIQUE（唯一）索引来保证数据的唯一性。
　　-  让我们尝试一个实例：下表中无索引以及主键，所以该表允许出现多条重复记录。
CREATE TABLE person_tbl　　
(
　　
    first_name CHAR(20),
　　
    last_name CHAR(20),
　　
    sex CHAR(10)
　　
);
　　-  如果你想要设置表中字段first_name、last_name熟不不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键的默认值不能为NULL，可设置为NOT NULL，如下所示：
CREATE TABLE person_tbl　　
(
　　
   first_name CHAR(20) NOT NULL,
　　
   last_name CHAR(20) NOT NULL,
　　
   sex CHAR(10),
　　
   PRIMARY KEY (last_name, first_name)
　　
);
　　-  如果我们设置了唯一索引，那么在插入重复数据的时候，SQL语句将无法执行成功，并抛出错误。
　　-  INSERT IGNORE INTO和INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据；如果有数据的话就跳过这条数据，这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
　　-  以下实例使用了INSERT IGNORE INTO，执行后不会出错，也不会像数据表中插入重复的数据：
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)　　
    -> VALUES( 'Jay', 'Thomas');
　　
Query OK, 1 row affected (0.00 sec)
　　
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
　　
    -> VALUES( 'Jay', 'Thomas');
　　
Query OK, 0 rows affected (0.00 sec)
　　-  INSERT IGNORE INTO插入数据时，在设置记录的唯一性后，如果插入重复数据，将不返回错误，只是以警告形式返回。而REPLACE INTO，如果存在PRIMARY KEY或者UNIQUE相同的记录，则先删除掉，再插入新的记录。
　　-   另一种设置数据唯一性方法是添加一个UNIQUE索引，如下所示：
CREATE TABLE person_tbl　　
(
　　
   first_name CHAR(20) NOT NULL,
　　
   last_name CHAR(20) NOT NULL,
　　
   sex CHAR(10)
　　
   UNIQUE (last_name, first_name)
　　
);
　　b、统计重复数据
　　-  以下我们将统计表中的first_name和last_name的重复记录数：
mysql> SELECT COUNT(*) as repetitions, last_name, first_name　　
    -> FROM person_tbl
　　
    -> GROUP BY last_name, first_name
　　
    -> HAVING repetitions > 1;
　　-  以上查询语句将返回person_tbl表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：
　　-  确定哪一列包含的值可能会重复。
　　-  在列选择列表中使用COUNT(*)列出的那些列。
　　-  在GROUP BY子句中列出的列。
　　-  HAVING子句设置重复数大于1。
　　c、过滤重复数据
　　-  如果你需要读取不重复的数据可以在SELECT语句中使用DISTINCT关键字来过滤重复数据。
mysql> SELECT DISTINCT last_name, first_name　　
    -> FROM person_tbl;
　　-  你也可以使用GROUP BY来读取数据表中不重复的数据：
mysql> SELECT last_name, first_name　　
    -> FROM person_tbl
　　
    -> GROUP BY (last_name, first_name);
　　d、删除重复数据
　　-  如果你想要删除数据表中的重复数据，你可以使用以下的SQL语句：
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex　　
    ->                  FROM person_tbl;
　　
    ->                  GROUP BY (last_name, first_name, sex);
　　
mysql> DROP TABLE person_tbl;
　　
mysql> ALTER TABLE tmp RENAME TO person_tbl;
　　-  当然你也可以在数据表中添加INDEX（索引）和PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
mysql> ALTER IGNORE TABLE person_tbl　　
    -> ADD PRIMARY KEY (last_name, first_name);
　　3、MySQL以及SQL的注入
　　-  如果你通过网页获取用户输入的数据并将其插入到一个MySQL数据库，那么就有可能发生SQL注入安全的问题。
　　-  所谓SQL注入，就是通过把SQL命令插入到Web表单提交或者输入域名或者页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
　　-  我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。以下实例中，输入的用户名必须是字母、数字以及下划线的组合，且用户名长度为8到20字符之间：
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))　　
{
　　
   $result = mysqli_query($conn, "SELECT * FROM users
　　
                          WHERE username=$matches[0]");
　　
}
　　
else
　　
{
　　
   echo "username 输入异常";
　　
}
　　-  让我们看下载没有过滤特殊字符时，出现的SQL情况：
// 设定$name 中插入了我们不需要的SQL语句　　
$name = "Qadir'; DELETE FROM users;";
　　
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");
　　-  以上的注入语句中，我们没有对$name的变量进行过滤，$name中插入了我们不需要的SQL语句，将删除users表中的所有数据。
　　-  防止SQL注入，我们需要注意以下几个有点：
　　a、永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或者限制长度。
　　b、对于单引号和双引号进行转换等，永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
　　c、永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限，进行有限的数据库连接。
　　d、不要把机密信息直接存放，必要时进行加密操作。
　　e、应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装。
　　f、sql注入的检测方法一般采取辅助软件或者网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具、MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效地防御SQL注入， XSS***等。