MySQL 5.1参考手册 :: 3. 教程(5)

3.6.7. 根据两个键搜索

可以充分利用使用单关键字的OR子句，如同AND的处理。

一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

SELECT field1_index, field2_index FROM test_table

WHERE field1_index = '1' OR  field2_index = '1'

该情形是已经优化过的。参见7.2.6节，“索引合并优化”。

还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。参见13.2.7.2节，“UNION语法
”。

每个SELECT只搜索一个关键字，可以进行优化：

SELECT field1_index, field2_index

    FROM test_table WHERE field1_index = '1'

UNION

SELECT field1_index, field2_index

    FROM test_table WHERE field2_index = '1';

3.6.8. 根据天计算访问量

下面的例子显示了如何使用位组函数来计算每个月中用户访问网页的天数。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

             day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

            (2000,2,23),(2000,2,23);

示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<

       GROUP BY year,month;

将返回：

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2000 |    01 |    3 |

| 2000 |    02 |    2 |

+------+-------+------+

该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

3.6.9. 使用AUTO_INCREMENT

可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：

CREATE TABLE animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

 );

INSERT INTO animals (name) VALUES 

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

将返回：

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | whale   |

|  6 | ostrich |

+----+---------+

你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。这些函数与具体连接有关，因此其返回值不会被其它执行插入功能的连接影响。

注释：对于多行插入，LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。在复制设置中，通过该函数可以在其它服务器上正确复制多行插入。

对于MyISAM和BDB表，你可以在第二栏指定AUTO_INCREMENT以及多列索引。此时，AUTO_INCREMENT列生成的值的计算方法为：MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要将数据放入到排序的组中可以使用该方法。

CREATE TABLE animals (

    grp ENUM('fish','mammal','bird') NOT NULL,

    id MEDIUMINT NOT NULL AUTO_INCREMENT,

    name CHAR(30) NOT NULL,

    PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name) VALUES 

    ('mammal','dog'),('mammal','cat'),

    ('bird','penguin'),('fish','lax'),('mammal','whale'),

    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

将返回：

+--------+----+---------+

| grp    | id | name    |

+--------+----+---------+

| fish   |  1 | lax     |

| mammal |  1 | dog     |

| mammal |  2 | cat     |

| mammal |  3 | whale   |

| bird   |  1 | penguin |

| bird   |  2 | ostrich |

+--------+----+---------+

请注意在这种情况下（AUTO_INCREMENT列是多列索引的一部分），如果你在任何组中删除有最大AUTO_INCREMENT值的行，将会重新用到AUTO_INCREMENT值。对于MyISAM表也如此,对于该表一般不重复使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL将使用该索引生成以AUTO_INCREMENT列开始的序列值。。例如，如果animals表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值时将忽略PRIMARY KEY。结果是，该表包含一个单个的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

关于AUTO_INCREMENT的详细信息：

·         如何为列指定AUTO_INCREMENT属性：13.1.5节，“CREATE TABLE语法”和 13.1.2节，“ALTER TABLE语法”。

·         AUTO_INCREMENT的动作取决于SQL模式：5.3.2节，“SQL服务器模式”。

·         找出含有最新AUTO_INCREMENT值的行：12.1.3节，“比较函数和操作符”。

·         设置将用到的AUTO_INCREMENT值: 13.5.3节，“SET语法” 。

·         AUTO_INCREMENT和复制：6.7节，“复制特性和已知问题”.

·         AUTO_INCREMENT相关的可用于复制的Server-system变量(auto_increment_increment和auto_increment_offset)：5.3.3节，“服务器系统变量”。

3.7. 孪生项目的查询

3.7.1. 查找所有未分发的孪生项

3.7.2. 显示孪生对状态的表

这个项目是Institute of Environmental Medicine at Karolinska Institutet Stockholm 和 the Section on Clinical Research in Aging and Psychology at the University of Southern California的合作项目。

该项目包括筛选部分，即通过电话回访在瑞典超过 65 岁的所有孪生。满足某种标准的孪生进入下一阶段。在下一阶段中，医生/护士小组将访问想参加的孪生。部分检查包括物理检查和神经、心理检查、实验室试验、神经成像、心理状况评估和家族历史搜集。并且，应根据医疗和环境风险因素来搜集数据。

可从以下链接找到孪生研究的更多信息：

用一个用Perl和MySQL编写的web接口来管理项目的后面部分。

每天晚上所有会谈的数据被移入一个MySQL数据库。

3.7.1. 查找所有未分发的孪生项

下列查询用来决定谁进入项目的第二部分：

SELECT
    CONCAT(p1.id, p1.tvab) + 0 AS tvid,
    CONCAT(p1.christian_name, ' ', p1.surname) AS Name,
    p1.postal_code AS Code,
    p1.city AS City,
    pg.abrev AS Area,
    IF(td.participation = 'Aborted', 'A', ' ') AS A,
    p1.dead AS dead1,
    l.event AS event1,
    td.suspect AS tsuspect1,
    id.suspect AS isuspect1,
    td.severe AS tsevere1,
    id.severe AS isevere1,
    p2.dead AS dead2,
    l2.event AS event2,
    h2.nurse AS nurse2,
    h2.doctor AS doctor2,
    td2.suspect AS tsuspect2,
    id2.suspect AS isuspect2,
    td2.severe AS tsevere2,
    id2.severe AS isevere2,
    l.finish_date
FROM
    twin_project AS tp
    /* For Twin 1 */
    LEFT JOIN twin_data AS td ON tp.id = td.id
              AND tp.tvab = td.tvab
    LEFT JOIN informant_data AS id ON tp.id = id.id
              AND tp.tvab = id.tvab
    LEFT JOIN harmony AS h ON tp.id = h.id
              AND tp.tvab = h.tvab
    LEFT JOIN lentus AS l ON tp.id = l.id
              AND tp.tvab = l.tvab
    /* For Twin 2 */
    LEFT JOIN twin_data AS td2 ON p2.id = td2.id
              AND p2.tvab = td2.tvab
    LEFT JOIN informant_data AS id2 ON p2.id = id2.id
              AND p2.tvab = id2.tvab
    LEFT JOIN harmony AS h2 ON p2.id = h2.id
              AND p2.tvab = h2.tvab
    LEFT JOIN lentus AS l2 ON p2.id = l2.id
              AND p2.tvab = l2.tvab,
    person_data AS p1,
    person_data AS p2,
    postal_groups AS pg
WHERE
    /* p1 gets main twin and p2 gets his/her twin. */
    /* ptvab is a field inverted from tvab */
    p1.id = tp.id AND p1.tvab = tp.tvab AND
    p2.id = p1.id AND p2.ptvab = p1.tvab AND
    /* Just the screening survey */
    tp.survey_no = 5 AND
    /* Skip if partner died before 65 but allow emigration (dead=9) */
    (p2.dead = 0 OR p2.dead = 9 OR
     (p2.dead = 1 AND
      (p2.death_date = 0 OR
       (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
        >= 65))))
    AND
    (
    /* Twin is suspect */
    (td.future_contact = 'Yes' AND td.suspect = 2) OR
    /* Twin is suspect - Informant is Blessed */
    (td.future_contact = 'Yes' AND td.suspect = 1
                               AND id.suspect = 1) OR
    /* No twin - Informant is Blessed */
    (ISNULL(td.suspect) AND id.suspect = 1
                        AND id.future_contact = 'Yes') OR
    /* Twin broken off - Informant is Blessed */
    (td.participation = 'Aborted'
     AND id.suspect = 1 AND id.future_contact = 'Yes') OR
    /* Twin broken off - No inform - Have partner */
    (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                  AND p2.dead = 0))
    AND
    l.event = 'Finished'
    /* Get at area code */
    AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
    /* Not already distributed */
    AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
    /* Has not refused or been aborted */
    AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
    OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
    tvid;

一些解释：

·         CONCAT(p1.id, p1.tvab) + 0 AS tvid

我们想要在id和tvab的连接上以数字顺序排序。结果加0使得MySQL把结果变为一个数字。

·         列id

这标识一对孪生。它是所有表中的一个键。

·         列tvab

这标识孪生中的一个。它的值为1或2。

·         列ptvab

这是tvab的一个逆运算。当tvab是1，它是2，反之亦然。它用来保存输入并且使MySQL的优化查询更容易。

这个查询表明，怎样用联结(p1和p2)从同一个表中查找表。在例子中，这被用来检查孪生的一个是否在65岁前死了。如果如此，行不返回值。

上述所有孪生信息存在于所有表中。我们对id,tvab（所有表）和id,ptvab (person_data) 上采用键以使查询更快。

在我们的生产机器上(一台200MHz UltraSPARC)，这个查询返回大约 150-200 行并且时间不超过一秒。

表	行数
person_data	71074
lentus	5291
twin_project	5286
twin_data	2012
informant_data	663
harmony	381
postal_groups	100

3.7.2. 显示孪生对状态的表

每一次会面以一个称为event的状态码结束。下面显示的查询被用来显示按事件组合的所有孪生的表。这表明多少对孪生已经完成，多少对的其中之一已完成而另一个拒绝了，等等。

SELECT

        t1.event,

        t2.event,

        COUNT(*)

FROM

        lentus AS t1,

        lentus AS t2,

        twin_project AS tp

WHERE

        /* We are looking at one pair at a time */

        t1.id = tp.id

        AND t1.tvab=tp.tvab

        AND t1.id = t2.id

        /* Just the screening survey */

        AND tp.survey_no = 5

        /* This makes each pair only appear once */

        AND t1.tvab='1' AND t2.tvab='2'

GROUP BY

        t1.event, t2.event;

3.8. 与Apache一起使用MySQL

还有一些项目，你可以从MySQL数据库鉴别用户，并且你还可以将日志文件写入MySQL数据库表。

你可以将以下内容放到Apache配置文件中，更改Apache日志格式，使MySQL更容易读取：

LogFormat \

        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \

        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

要想将该格式的日志文件装载到MySQL，你可以使用以下语句:

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

所创建的表中的列应与写入日志文件的LogFormat行对应。

---

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。 原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。