dotancohen's questions

在 Mysql 8.0.28 中，我注意到一些元数据行，例如“集合中的 N 行”，即使它们与寻呼机设置不匹配也会显示。例如：

mysql> pager grep address
PAGER set to 'grep address'
mysql> show tables;
| customer_address_entity                                |
| customer_address_entity_datetime                       |
| customer_address_entity_decimal                        |
| customer_address_entity_int                            |
| customer_address_entity_text                           |
| customer_address_entity_varchar                        |
| inventory_pickup_location_quote_address                |
| quote_address                                          |
| quote_address_item                                     |
| sales_order_address                                    |
450 rows in set (0.01 sec)

在什么级别添加这些元数据行，在什么级别应用寻呼机？元数据行是否作为替代文件描述符发送，类似于某些stdmeta 文件描述符甚至 stderror（curl与元数据一样）？或者寻呼机是在客户端应用的，而不是在服务器上应用的？

我有一个要调试的应用程序，它对 MySQL 数据库执行几十个操作。这些操作在单个事务中执行。在特定断点进行调试时，我需要知道数据库的状态。

mysql我可以使用常用的CLI 工具登录 MySQL 服务。我看到正在进行的交易：

mysql> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 325090
                 trx_state: RUNNING
               trx_started: 2022-09-14 12:10:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 30
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 10
          trx_lock_structs: 26
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 43
         trx_rows_modified: 4
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.01 sec)

现在，如何加入该事务以在事务期间查询数据库的状态？

作为一种解决方法，我可以向应用程序代码添加查询。但是，这需要我在运行应用程序之前知道要运行哪个查询，然后为要运行的每个查询重新编译应用程序。有一个开放的 CLI 来调试会非常有帮助。

该数据库是在 Debian 上运行的 MySQL 8.0。

考虑一个 EAV 表，每个数据类型都有单独的列：

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;

现在我想存储username列 for entity_id=5，它是一个 VARCHAR：

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');

如何构造 SELECT 语句以通过单个查询从正确的列中进行选择？

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

我最好只是合并值并仔细管理应用程序代码中的数据吗？这样的 SELECT 没有真正的性能损失吗？

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

或者可能将数据复制到冗余readVARCHAR 列中以供读取？

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

请注意，我想为每个值使用不同的数据类型，因为在某些情况下我可能需要在某些列上建立索引或执行特定于数据类型的逻辑，在这些情况下我会知道哪种数据类型或者我将使用单独的 SELECT 来获取列类型。

如何在SELECT查询的子句中增加一个“固定”值，以便每一行都有不同的值集？对于说明意图的明显无效的示例：

SELECT
    name, ++x as ordinal
FROM
    foo;

name   | ordinal
----------------
john   | 0
paul   | 1
george | 2
ringo  | 3

最终目的是从数据库中选择用户来获得一、二、三等奖。我会让一个用户获得一等奖，五个二等奖获得者，二十个三等奖获得者：

SELECT
    name, IF(++x<2, 'first',  IF(x<7, 'second', 'third') ) as prize
FROM
    foo LIMIT 26;

name     | prize
------------------
alice    | first
bob      | second
charlie  | second
dotan    | second
erik     | second
frank    | second
george   | third
henry    | third
:        | :
:        | :

这是在 MySQL v5.5 上运行的。谢谢你。

对于以下查询，我有一个覆盖索引：

SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent;

ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id);

现在我有另一个查询，我想为其添加一个覆盖索引：

SELECT user_id FROM foo WHERE bar_id=:bar_id AND date_sent=:date_sent;

之前的覆盖索引是否也覆盖了这个新查询？当我运行EXPLAIN新查询时，返回以下输出：

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | ref  | bar_id        | bar_id | 4       | const |    5 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+

我看到提到的关键是bar_id，但在我看来这表明这不是覆盖索引。此外，该Extra字段没有显示预期的Using index输出，使我确信正在使用覆盖索引。

我有一个特定的表，我无法为其添加键：

mysql> ALTER TABLE tasks ADD KEY `fruitful_user_count` (`user_id`, `is_fruitful`);
ERROR 1034 (HY000): Incorrect key file for table 'tasks'; try to repair it

谷歌搜索这个问题似乎这个问题通常是配置问题或磁盘空间问题。事实上，这个数据库运行在一个Amazon RDS实例上，这意味着它基本上是一个专用于 MySQL 的托管服务器，具有非常标准的配置。此外，分配给我们的磁盘只有大约 25% 已满。

考虑到虚拟机上的磁盘（我相信由 Xen 提供支持）可能已满，而不是我分配的磁盘空间甚至可能不在同一个房间（网络存储），我重新启动了 RDS 实例，希望我能得到另一个 VM 上的新实例。然而，这并没有帮助。

我的下一个故障排除步骤应该是什么？

这是表格：

mysql> show create table tasks;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`user_id` char(32) NOT NULL,
`module_id` int(11) NOT NULL DEFAULT '0',
`is_successful` tinyint(1) DEFAULT NULL,
`is_fruitful` tinyint(1) DEFAULT NULL,
`last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_pulled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_id`,`module_id`),
KEY `phone_scrapes_user` (`user_id`),
KEY `phone_scrapes_module` (`module_id`),
KEY `urgency` (`last_pulled`,`last_run`),
KEY `successful_user_count` (`user_id`,`is_successful`),
KEY `is_successful` (`is_successful`),
KEY `fruitness` (`is_fruitful`,`is_successful`),
CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

在全新的 Kubuntu 14.04 安装上，我运行了sudo aptitude install mysql-server-core-5.6. 由于某些 KDE 软件包对 的依赖，安装无法完成，mysql-server-core-5.5这些软件包将被替换。现在，当我运行时sudo aptitude install mysql-server-5.5，指定密码后出现此错误：

Configuring mysql-server-5.5
Unable to set password for the MySQL "root" user
An error occurred while setting the password for the MySQL administrative user. This may have happened
because the account already has a password, or because of a communication problem with the MySQL server.
You should check the account's password after the package installation.
Please read the /usr/share/doc/mysql-server-5.5/README.Debian file for more information.

我实际上已经阅读过/usr/share/doc/mysql-server-5.5/README.Debian，但与我的情况无关。MySQL 日志有助于：

$ tail /var/log/mysql/error.log 
140818 10:17:16 InnoDB: Completed initialization of buffer pool
140818 10:17:16 InnoDB: highest supported file format is Barracuda.
140818 10:17:16  InnoDB: Waiting for the background threads to start
140818 10:17:17 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:17:17 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:17:17 [ERROR] Aborting

140818 10:17:17  InnoDB: Starting shutdown...
140818 10:17:18  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:17:18 [Note]

但是我找不到在哪个脚本--explicit_defaults_for_timestamp中设置了该选项。我试图在mysql没有service避免该--explicit_defaults_for_timestamp选项的情况下启动，但它仍然无法启动：

$ ps aux | grep mysql
dotanco+ 25458  0.0  0.0  11748   928 pts/4    S+   10:30   0:00 grep --color=auto mysql

$ sudo mysqld_safe --skip-grant-tables &
[1] 25470
140818 10:30:54 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.
140818 10:30:54 mysqld_safe Logging to '/var/log/mysql/error.log'.
140818 10:30:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140818 10:30:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

$ ps aux | grep mysql
dotanco+ 25810  0.0  0.0  11748   932 pts/4    S+   10:31   0:00 grep --color=auto mysql

错误告诉我Remove all --log-error configuration options。因此，我编辑/etc/mysql/my.cnf并注释掉了以下行：

log-error  = /var/log/mysql/error.log

现在我没有收到错误但仍然无法登录：

$ sudo mysqld_safe --skip-grant-tables &
[1] 26558
140818 10:34:37 mysqld_safe Logging to syslog.
140818 10:34:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140818 10:34:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

可能是什么问题？我应该如何进行？

编辑

我现在已经注释掉explicit-defaults-for-timestamp了my.cnf. 这是整个文件，减去标题注释：

$ cat /etc/mysql/my.cnf 

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#explicit_defaults_for_timestamp

bind-address    = 127.0.0.1

#log-error      = /var/log/mysql/error.log

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

symbolic-links=0

!includedir /etc/mysql/conf.d/

这是我尝试运行 MySQL 时发生的情况：

$ sudo echo 1 >> /var/log/mysql/error.log

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

$ sudo service mysql start
start: Job failed to start

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

$ grep explicit_defaults_for_timestamp /etc/mysql/my.cnf /etc/init.d/mysql /etc/default/mysql
/etc/mysql/my.cnf:#explicit_defaults_for_timestamp
grep: /etc/default/mysql: No such file or directory

可以看出，日志中没有写入任何新内容，explicit_defaults_for_timestamp也没有在任何地方设置该选项。

我能够重新安装mysql-server-core-5.5，但这并没有解决问题：

$ sudo aptitude reinstall mysql-server-core-5.5
The following packages will be REINSTALLED:
  mysql-server-core-5.5 
0 packages upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 0 not upgraded.
Need to get 3,213 kB of archives. After unpacking 0 B will be used.
Get: 1 http://il.archive.ubuntu.com/ubuntu/ trusty-updates/main mysql-server-core-5.5 amd64 5.5.38-0ubuntu0.14.04.1 [3,213 kB]
Fetched 3,213 kB in 1s (3,163 kB/s)                
(Reading database ... 217167 files and directories currently installed.)
Preparing to unpack .../mysql-server-core-5.5_5.5.38-0ubuntu0.14.04.1_amd64.deb ...
Unpacking mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) over (5.5.38-0ubuntu0.14.04.1) ...
Replaced by files in installed package mysql-common (5.6.20-1ubuntu14.04) ...
Processing triggers for man-db (2.6.7.1-1) ...
Setting up mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) ...

$ sudo service mysql status
mysql stop/waiting

$ sudo service mysql start
start: Job failed to start

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

1日志最后一行的那个是之前添加的，表示日志中echo没有添加任何新内容。

ibdata1在 Ubuntu Server 12.10 机器上，在阅读Rolando 关于具体操作方法的解释之前，我曾尝试删除该文件。innodb_fast_shutdown = 0具体来说，我在关闭MySQL之前没有设置。

ibdata1既然已删除但innodb_fast_shutdown尚未设置，我该如何启动 MySQL ？

我有所有数据库的备份，但我无法重新安装 MySQL，因为不再支持 Ubuntu 版本。由于当前版本中的可访问性问题，我无法将 Ubuntu 更新到受支持的版本，也无法降级到 12.04 LTS（仍然受支持），因为 Ubuntu 从未设计为可降级，我需要的一些应用程序无法在该版本上运行良好如果我完全重新安装旧版本，因为它们依赖于不在 12.04 中而是在 12.10 中的一系列依赖项。

考虑一个值和哈希表，如下所示：

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| val        | char(9)  | NO   |     | NULL    |                |
| val_hashed | char(50) | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

以下查询在 0.00 秒内完成：

SELECT * FROM hashes ORDER BY 1 DESC LIMIT 1;

但是，此查询需要 3 分 17 秒：

SELECT val FROM hashes ORDER BY 1 DESC LIMIT 1;

我看到，当查询运行时，进程列表将其显示为 status Sorting result。这种情况是完全可以重现的。请注意，还有另一个进程INSERT连续对表执行操作。

为什么更具体的查询会比查询花费更长的时间来运行*？我一直认为，*出于性能原因，应该专门避免查询。

如何在子查询中引用当前分组的字段？

考虑一个包含以下数据的表：

Client    Status    Size    
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

我试图在单个查询中获取以下数据：

Client     A      B
   1      500   1500
   2     2000    800

我一直在玩这方面的变体，但绝对没有成功：

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

每个客户端的状态不重复，也就是说，每个客户端应该只有一行具有给定的状态。出于这个问题的目的，我们可以假设每种状态只有一个条目。

请注意，大约有十万行：大约 2 万个客户端和每个客户端五个状态。还存在其他字段（例如状态更新日期和执行更新的用户），因此将表模式更改为简单Client, A, B是不可行的。

如何在子查询中引用分组字段？

我有一个大约 1700 万行的表：

mysql> describe humans_we_respect;
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| Field               | Type                                                                    | Null | Key | Default | Extra |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| id                  | bigint(20)                                                              | NO   | PRI | NULL    |       |
| name                | varchar(63)                                                             | YES  |     | NULL    |       |
| address             | varchar(127)                                                            | YES  |     | NULL    |       |
| city                | varchar(63)                                                             | YES  |     | NULL    |       |
| state               | varchar(3)                                                              | YES  | MUL | NULL    |       |
| zip                 | varchar(15)                                                             | YES  |     | NULL    |       |
| country             | varchar(15)                                                             | YES  |     | NULL    |       |
| email               | varchar(127)                                                            | YES  |     | NULL    |       |
| website             | varchar(127)                                                            | YES  |     | NULL    |       |
| area_code_state     | varchar(3)                                                              | YES  | MUL | NULL    |       |
| timezone            | set('other','pacific','mountain','central','eastern','alaska','hawaii') | YES  |     | other   |       |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

由于只联系那些对时事通讯表示兴趣的人的严格性质，以及从不联系要求不联系的人的严格性质，在邮寄之前，我添加了一个字段，我为那些表示有兴趣的人expressed_interest (tinyint) deafult null切换到，然后1切换到null那些要求不被联系的人。

以下查询（每个查询更新 10000 行）需要很长时间才能运行（半小时后终止）：

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

但是，以下查询在几秒钟内完成：

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

在什么条件下会ON DUPLICATE KEY UPDATE比 快UPDATE？我想知道这一点，以备将来与这样的大表一起使用。

这是在Amazon RDS中运行的 MySQL 5.5.33 上。

这篇非常有用的文章解释了有关MySQL 枚举行为的一些奇怪之处：http : //melp.nl/2009/05/mysql-enums-and-booleans/

没有讨论为什么存在这种行为。此外，在谷歌搜索这个主题时，我发现只有“博主”和其他盲人引导盲人的相互矛盾的信息和猜测。

有人可以描述为什么 MySQL 枚举会遭受帖子中概述的行为的影响。这是博文中出现的枚举异常的一个示例，但还有更多：

mysql> insert into t values('1'),('0');
mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)

考虑一个status包含两个值之一的字段的表：

| status | set('active','default') | YES  |  | active |

我需要知道表中有多少条记录，还有多少条status设置为active. 我注意到每个记录集的SUM返回值和每个记录集的返回值：1active2default

mysql> select count(*), sum(status) from users;
+----------+-------------+
| count(*) | sum(status) |
+----------+-------------+
|        3 |           4 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select status from users;
+---------+
| status  |
+---------+
| default |
| active  |
| active  |
+---------+
3 rows in set (0.00 sec)

我可以依赖这种行为吗？这是一种不好的做法吗？

我正在审核我在几周内开发的应用程序。我注意到了这一点CREATE TABLE：

UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`),
KEY `baz` (`baz`),

KEY正如我认为的那样，显式是多余的吗？我只是想先确定DROP一下。从我读过的内容来看，我确实相信 UNIQUE KEY当应用程序执行时，它将充当索引，WHERE baz='something'但 MySQL 似乎非常复杂，我发现首先在这里询问是明智的。

根据关于允许 dba.SE 上的基本 SQL 问题的 Meta 讨论，我提出了一个我现在遇到的问题，对于 Stack Overflow 上的答案是不充分且幼稚的。我希望有比 SO 上提出的问题更好的解决方案（因为我目前在应用程序中面临这个问题），并且 dba.SE 似乎是找到更好答案的理想场所。

这是 Stack Overflow 上的原始问题：如何从 mysql 中选择每 n 行？

这是公认的答案：

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1 

接受答案的关键问题是它需要将整个表拉到一个临时表中。因此，我已经在这个问题的标题中解决了这个问题。

还要考虑该表可能已删除行，因此仅对WHERE MOD主键进行测试的替代查询也不是一个好的解决方案。Id est，不能相信主键是连续的。

有没有更好的方法来表达一个查询，该查询将返回每第二、第十或任意第 n 行，它不需要将整个表拉入内存但也考虑已删除的行？

每第 n 行可以这样定义：

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

我的目标数据库是 MySQL 5.5，它运行在一个常见的 Debian 派生 Linux 发行版上。

编辑：回应托马斯的回答：

建议的解决方案不会产生预期的结果，见下文：

mysql> SELECT 
    ->     @i:=@i+1 AS iterator 
    ->     , t.name
    -> FROM 
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)

如何检索在加入时没有特定字段 ( sent)，或者如果它们确实有该字段 ( sent) 然后failure_reason每一sent行都有一个附加字段 ( ) 的项目？

考虑一个有两个表的数据库：

list
  id INT
  name VARCHAR

log
  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

典型list内容：

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

典型log内容：

ID | List_id | Action  | Failure Reason
---+---------+---------+---------------
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

可以看出 List_id1有 和 的日志条目entered，sent没有failure_reason. 因此，此项目已由我负责。

同样，List_id2有一个log条目用于entered，还有两个条目用于sent。这是因为第一次sent失败了。我们知道第一次发送失败，因为failure_reason它不是 NULL。既然有sent排成功，这一项也就交给我负责了。

但是，List_id3有一个log条目entered，但唯一的sent一行是失败的。因此，这个项目仍然是我的责任，应该在查询中检索。

此外，List_id4有一个log条目entered，但没有sent行。因此，这个项目仍然是我的责任，应该在查询中检索。

我曾尝试使用一些子查询魔术来根据sent值获取/排除行，但是随着该表变得越来越大（预计每天有数千条新记录），我需要避免需要返回整个数据库中所有结果的查询。

这是在 CentOS 6.x 上的 MySQL 5.1 中。

我有两张表，我只需要一张表 ( list) 中没有出现在第二张表 ( cardinal) 中的结果。该list表的主键是sku，并且该表有一个痕迹id列（目前在应用程序中实际上未使用）。该cardinal表的主键是id。应该加入表的列是sku。这些是表格（仅相关字段，还有其他字段我没有在这里粘贴）：

mysql> describe list;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | int(11)      | NO   |     | NULL    |       |
| name            | varchar(63)  | YES  |     | NULL    |       |
| sku             | bigint(20)   | NO   | PRI | 0       |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe cardinal;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| sku              | bigint(20)  | YES  | MUL | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

以下是每个表中的记录数量：

mysql> SELECT count(*) FROM list;
+----------+
|  2677513 |
+----------+

mysql> SELECT count(*) FROM cardinal;
+----------+
|   970924 |
+----------+

表中的每条记录cardinal都有该sku列的有效条目，并且所有这些有效条目都存在于list表中。表中确实存在一些欺骗。skucardinal

我需要list表中没有对应条目的所有记录cardinal。但是，我尝试的所有方法要么返回list表中的所有记录，要么根本没有记录！

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;
+----------+
| count(*) |
+----------+
|  2677513 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

第一个查询显然不知道在sku列上匹配。但是第二个看起来应该基于 我在网上看到的 其他答案 。

我预计将返回大约 1700000 - 2000000 行。但肯定不是 0 或全表。我应该如何措辞查询？这是在 Amazon RDS 中运行的 MySQL 5.5.33。

考虑以下查询：

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

INSERT在这两个查询运行之间，数据库上没有运行任何查询或其他查询。第二个查询似乎比第一个查询更具限制性，那么它如何返回更多行呢？请注意，该list表有超过 200 万行，而该cardinal表大约有 50 万行。

编辑：添加EXPLAIN输出：

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)

考虑一些家庭成员的表格：

mysql> SELECT * FROM some_table;
+--------+--------+--------+
| f_name | l_name | work   |
+--------+--------+--------+
| Meirav | Cohen  | school |
| Dotan  | Cohen  |        |
+--------+--------+--------+
2 rows in set (0.00 sec)

假设f_name是主键，并且所有列都是VARCHAR. 未知数据不表示为 NULL，而是表示为空字符串（如work列中所示）。

我想添加一些我知道的关于一个人的数据，但我不想查询数据库以查看其中的内容，而是让数据库将现有值与新值合并：

mysql> INSERT INTO some_table (f_name, l_name, work)
    VALUES ('Dotan', '', 'php')
    ON DUPLICATE KEY UPDATE
        f_name = IFNULL(f_name, 'Dotan'),
        l_name = IFNULL(l_name, ''),
        work   = IFNULL(work,   'php');

当然，IFNULL不适合这项工作，因为它只检查 NULL，而不是空字符串。此外，还有一个缺点是要添加的每个值必须在 SQL 中出现两次。

有没有办法让数据库将数据与空字符串合并？另外，是否可以使要插入的字符串在 SQL 中只出现一次？

我在AWS RDS Medium 实例上有一个大型 MySQL 数据库表（约 100 万行并且还在增长）：

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

我经常需要执行以下查询的变体：

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

以及相关的EXPLAIN：

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

我应该进行哪些优化？我应该在 and 字段上添加索引name，zip还是在addedandsource字段上添加索引？