MySQL学习记录（八）-序列、处理重复数据、SQL注入

序列

用途：
一张数据表只能有一个字段自增主键，那我们需要多个字段自动增加应该怎么办？
所以我们引入MySQL序列来解决这个问题。

AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
举例：

CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,

获取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 charset=utf8;

也可：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

处理重复数据

防止出现重复数据

可以通过设置指定字段为 **PRIMARY KEY（主键）**或者 **UNIQUE（唯一）**索引来保证数据的唯一性

如果我们想要俩个字段的数据不能重复，我们可以设置双主键模式来设置数据的唯一性，但是注意：主键的默认值不能为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)
);

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 或 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)
);

统计重复数据

语法

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。

过滤重复数据

读取不重复的数据：在 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);

删除重复数据

方法1：直接删除

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;

方法二：利用主键和索引 （特性就是不能重复）

mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);

SQL注入

SQL注入
指通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
永远不要信任用户的输入，必须认定用户输入的数据都是不安全的，对所有用户输入的数据都进行过滤处理。

防止SQL注入，我们需要注意以下几个要点：

1. 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2. 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT
7. SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

总结

本文介绍了MySQL序列使用的方法，如何处理重复数据以及SQL注入需要注意的点，便于复习和回顾。

最后

以上就是勤奋飞鸟为你收集整理的MySQL学习记录（八）-序列、处理重复数据、SQL注入的全部内容，希望文章能够帮你解决MySQL学习记录（八）-序列、处理重复数据、SQL注入所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。