MySQL 元数据
- 获取查询语句影响的记录数
  - PERL 实例
  - PHP 实例
- 数据库和数据表列表
  - PERL 实例
  - PHP 实例
MySQL 序列使用
MySQL 处理重复数据
MySQL 导出数据
MySQL 导入数据

MySQL往期文章
mysql1> 安装与管理，安装，管理

| mysql2> PHP语法，mysql连接

| mysql3> 创建数据库，删除数据库，选择数据库

| mysq7> 元数据，序列使用，处理重复数据，及SQL注入，导出数据，导入数据

| | mysql4> 数据类型，创建数据表，删除数据表，插入数据，查询数据

| mysql5> where子句，UPDATE查询，DELETE语句，LIKE子句，排序，Join的使用

| mysql6> Null值处理，正则表达式，事务，ALTER命令，索引，临时表，复制表

| |

MySQL 元数据

你可能想知道MySQL以下三种信息：

查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。但如果使用Perl或PHP等脚本语言，你就需要调用特定的接口函数来获取。接下来我们会详细介绍。

获取查询语句影响的记录数

PERL 实例

在 DBI 脚本中，语句影响的记录数通过函数do( )或execute( )返回：

# 方法 1
# 使用do( ) 执行  $query
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d rows were affected\n", (defined ($count) ? $count : 0);
# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP 实例

在PHP中，你可以使用mysql_affected_rows( )函数来获取查询语句影响的记录数。

$result_id = mysql_query ($query, $conn_id);
# 如果查询失败返回
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

数据库和数据表列表

你可以很容易的在MySQL服务器中获取数据库和数据表列表。如果你没有足够的权限，结果将返回 null。
你也可以使用 SHOW TABLES或SHOW DATABASES 语句来获取数据库和数据表列表。

PERL 实例

# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "Table Name $table\n";
}

PHP 实例

<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
$db_list = mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysql_close($con);
?>

以下命令语句可以在MySQL的命令提示符使用，也可以在脚本中使用，如PHP脚本。

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

MySQL 序列使用

MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
本章我们将介绍如何使用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( ) 函数来获取最后的插入表中的自增列的值。
在PHP或PERL脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。

PERL实例

使用 mysql_insertid 属性来获取AUTO_INCREMENT 的值。实例如下：

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP实例

PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中AUTO_INCREMENT列的值。

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_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 = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

或者你也可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。
本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

防止表中出现重复数据

你可以在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 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)
);

统计重复数据

以下我们将统计表中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。
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用DISTINCT关键字来过滤重复数据。
```
mysql> SELECT DISTINCT last_name, first_name
  -> FROM person_tbl
  -> ORDER BY last_name;
```
你也可以使用 GROUP BY 来读取数据表中不重复的数据：
```
mysql> SELECT last_name, first_name
  -> FROM person_tbl
  -> GROUP BY (last_name, first_name);
```
删除重复数据
如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：
```
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
  ->                  FROM person_tbl;
  ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
```
当然你也可以在数据表中添加INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下： ```php mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);

<a name="Yo7O4"></a>
# MySQL 及 SQL 注入
如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。<br />本章节将为大家介绍如何防止SQL注入，并通过脚本来过滤SQL中注入的字符。<br />所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。<br />我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。<br />以下实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间：
```php
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users
                          WHERE username=$matches[0]");
}
 else
{
   echo "username 输入异常";
}

让我们看下在没有过滤特殊字符时，出现的SQL情况：

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

以上的注入语句中，我们没有对 $name 的变量进行过滤，$name 中插入了我们不需要的SQL语句，将删除users 表中的所有数据。
在PHP中的 mysql_query()是不允许执行多个SQL语句的，但是在 SQLite 和PostgreSQL 是可以同时执行多条SQL语句的，所以我们对这些用户的数据需要进行严格的验证。
防止SQL注入，我们需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双”-“进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
v 4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
防止SQL注入
在脚本语言，如Perl和PHP你可以对用户输入的数据进行转义从而来防止SQL注入。
PHP的MySQL扩展提供了mysql_real_escape_string()函数来转义特殊的输入字符。 ```php if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query(“SELECT * FROM users WHERE name=’{$name}’”);

<a name="rMGMH"></a>
## Like语句中的注入
`like`查询时，如果用户输入的值有`"_"_`_和_`_"%"_`_，则会出现这种情况：用户本来只是想查询_`_"abcd_"`，查询结果中却有`"abcd_"`、`"abcde"`、`"abcdf"`等等；用户要查询"30%"（注：百分之三十）时也会出现问题。<br />在PHP脚本中我们可以使用`addcslashes()`函数来处理以上情况，如下实例：
```php
$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes() 函数在指定的字符前添加反斜杠。
语法格式:

addcslashes(string,characters)

参数	描述
string	必需。规定要检查的字符串。
characters	可选。规定受 addcslashes() 影响的字符或字符范围。

具体应用可以查看：PHP addcslashes() 函数

MySQL 导出数据

MySQL中你可以使用**SELECT...INTO OUTFILE**语句来简单的导出数据到文本文件上。

使用 SELECT … INTO OUTFILE 语句导出数据

以下实例中我们将数据表 tutorials_tbl数据导出到 /tmp/tutorials.txt文件中:

mysql> SELECT * FROM tutorials_tbl 
    -> INTO OUTFILE '/tmp/tutorials.txt';

你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT … INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则SELECT ... INTO OUTFILE 不会起任何作用。
在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。
导出表作为原始数据
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
使用mysqldump导出数据需要使用--tab选项来指定导出文件指定的目录，该目标必须是可写的。
以下实例将数据表tutorials_tbl导出到/tmp目录中： ```php $ mysqldump -u root -p —no-create-info \
```
      --tab=/tmp TUTORIALS tutorials_tbl
```
password **

<a name="zKKTy"></a>
## 导出SQL格式的数据
导出SQL格式的数据到指定文件，如下所示：
```php
$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

以上命令创建的文件内容如下：

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58
--
-- Table structure for table `tutorials_tbl`
--
CREATE TABLE tutorials_tbl (
  tutorial_id int(11) NOT NULL auto_increment,
  tutorial_title varchar(100) NOT NULL default '',
  tutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorial_id),
  UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE=MyISAM;
--
-- Dumping data for table `tutorials_tbl`
--
INSERT INTO tutorials_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

如果你需要导出整个数据库的数据，可以使用以下命令：

$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

如果需要备份所有数据库，可以使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

--all-databases选项在 MySQL 3.23.12 及以后版本加入。
该方法可用于实现数据库的备份策略。

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在mysqldump 命令中指定数据库名及数据表。
在源主机上执行以下命令，将数据备份至dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整备份数据库，则无需使用特定的表名称。
如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</p>
$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

MySQL 导入数据

MySQL中可以使用两种简单的方式来导入MySQL导出的数据。

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。
以下实例中将从当前目录中读取文件dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。
你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。
两个命令的FIELDS 和 LINES子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS子句必须出现在LINES 子句之前。
如果用户指定一个FIELDS子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。
如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt'
    -> INTO TABLE mytbl (b, c, a);

使用 mysqlimport 导入数据

mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。
mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。
从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local database_name dump.txt
password *****

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

mysqlimport的常用选项介绍

选项	功能
-d or —delete	新数据导入数据表中之前删除数据数据表中的所有信息
-f or —force	不管是否遇到错误，mysqlimport将强制继续插入数据
-i or —ignore	mysqlimport跳过或者忽略那些有相同唯一关键字的行，导入文件中的数据将被忽略。
-l or -lock-tables	数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。
-r or -replace	这个选项与－i选项的作用相反；此选项将替代表中有相同唯一关键字的记录。
—fields-enclosed- by= char	指定文本文件中数据的记录时以什么括起的，很多情况下数据以双引号括起。默认的情况下数据是没有被字符括起的。
—fields-terminated- by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。您可以用此选项指定数据之间的分隔符。默认的分隔符是跳格符（Tab）
—lines-terminated- by=str	此选项指定文本文件中行与行之间数据的分隔字符串或者字符。默认的情况下mysqlimport以newline为行分隔符。您可以选择用一个字符串来替代一个单个的字符：一个新行或者一个回车。

mysqlimport命令常用的选项还有-v显示版本（version）， -p 提示输入密码（password）等。

技术博客学习笔记

MySQL（七）

MySQL 元数据

获取查询语句影响的记录数

PERL 实例

PHP 实例

数据库和数据表列表

PERL 实例

PHP 实例

MySQL 序列使用

使用AUTO_INCREMENT

实例

获取AUTO_INCREMENT值

PERL实例

PHP实例

重置序列

设置序列的开始值

MySQL 处理重复数据

防止表中出现重复数据

统计重复数据

过滤重复数据

删除重复数据

防止SQL注入

MySQL 导出数据

使用 SELECT … INTO OUTFILE 语句导出数据

SELECT … INTO OUTFILE 语句有以下属性:

导出表作为原始数据

将数据表及数据库拷贝至其他主机

MySQL 导入数据

使用 LOAD DATA 导入数据

使用 mysqlimport 导入数据

mysqlimport的常用选项介绍