Mark D's questions

我有下表：

CREATE TABLE `Channels` (
   `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255) NOT NULL,
   `views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;

INSERT INTO Channels (title, views) 
VALUES ('Movies', 50030), 
       ('TV shows', 51201), 
       ('Howtos', 18601);

CREATE TABLE `Videos` (
   `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
   `channel_id` INT(11),
   `name` VARCHAR(255) NOT NULL,
   `description` TEXT,
   `views` INT(11) NOT NULL DEFAULT 0,
   `created_at` DATETIME NOT NULL,
   `updated_at` DATETIME,
   CONSTRAINT videos_channel_id_fk
       FOREIGN KEY ( channel_id ) 
           REFERENCES Channels ( id )
) ENGINE=InnoDB;

INSERT INTO Videos (`channel_id`, `name`, `created_at`) 
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'), 
       (1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
       (2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
       (2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');

我正在尝试获取包含最新视频的不同频道列表。

例如，对于频道 1，它将是“2019-10-03 01:55:55”的视频“D”，对于频道 2，它将是“2019-10-03 01:58:00”的视频“E” );

我试过像这样自我加入，

SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at 
WHERE v1.created_at IS NULL

但我的视频 ID 与最新视频不匹配。即我最终会得到视频 ID：1，我想要视频 ID：4。

我在 SQL Server 2008 中有下表。

create table OrgUnits
(
    OrgUnitId int not null constraint PK_OrgUnits1 primary key,
    OrgUnitName varchar(100),
    OrgUnitLevel int not null,
    OrgUnitKey varchar(100) constraint UQ_OrgUnits_OrgUnitKey unique,
    Level1Id int not null,
    Level2Id int,
    StatusId int not null constraint FK_OrgUnits_Status references Status,
    VersionNumber int not null
);

INSERT INTO OrgUnits (OrgUnitId, OrgUnitName, OrgUnitKey, Level1Id, Level2Id,
                      StatusId, VersionNumber) 
VALUES 
(218824, 'Company1 (5) (5) (5)', '5', 218824, 1, 1),
(218825, 'Division1 (1) (1) (1)', '1', 218825, 1, 1),
(218826, 'XAVAT (4) (4) (4)', '4', 218826, 1, 1),
(218827, 'WATe (7) (7) (7)', '7', 218827, 1, 1),
(218828, 'Communications', '05-905-6320-300', 218824, 218828, 1, 1),
(218829, 'Corporate Development', '05-955-6320-300', 218824, 218829, 1, 1),
(218830, 'Corporate Board of Directors', '05-111-3515-301', 218830, 1, 1);        

理想情况下，我会OrgUnitName看起来像OrgUnitName + ' (' + OrgUnitKey + ')'; 但看起来更新已在表中的某些但不是所有值上运行。

更新语句是什么来更新所有行以使它们具有正确的格式？请注意我有数百行，这只是为了修复先前更新引起的早期问题。查询只需要运行一次。

我有下表

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo) 
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

问题是这样的，我想收集一份错误列表，但前提是将来没有解决办法。

理想情况下我会得到这样的回报

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

只是因为将来没有一些 TaskId 存在异常。

所以我想我可以通过一组子查询来做到这一点，如下所示：

SELECT * 
FROM TaskJournal 
WHERE TaskId = 2 
GROUP BY TaskId, TaskStatus 
HAVING TaskStatus=3 
ORDER BY TaskStart DESC

但问题在于它返回第 4 行和第 2 行（按时间降序排列）。但在稍后的某个时候，似乎已经解决了 TaskId=2 而不是 TaskId=3。

任何想法都会有所帮助。

是否可以使用游标创建动态表，然后使用这些列来聚合 SQL Server 2008 中的数据？

以下表为例。

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235); 

我想做的是用这种格式创建一个表

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

我能想到的唯一方法是聚合垂直表。

通过执行以下查询

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost) 
FROM BillingData;

但是我必须将这些列动态地加入到帐单表中，特别是因为帐单数据可能每个月都不一样。例如：

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

不同于

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

因此，虽然 BillingId、Walks、Baths、Litter change、Groom、Scratching、Usage、Cost 列适用于 4 月，但 May 的列将只是 BillingId、Pilling、Groom、Exercise、Usage 和 Cost。

我相信数据透视表可能是我在这里需要的，但我怀疑它可能需要是动态的，因为每个月的列都需要不同。

我不确定执行此操作的最佳方法。一些帮助将不胜感激。

假设你有一张像

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

我不确定的是为什么

SELECT COUNT(1) FROM t1;

需要 19 分钟（我已经用 120 万行填充了表格）。

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

我想知道这里的问题是否是基数。我的问题是 InnoDB 索引是否应该按更靠近索引开始的更高基数列排序，即PRIMARY KEY (provider_id,search_id);它是否重要？

我正在尝试从表中删除重复项。

CREATE TABLE media_views (
  `companyid` INT (11) NOT NULL,
  `channel` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `views` INT(11) NOT NULL DEFAULT 0
  PRIMARY KEY (`companyid`,`channel`,`date`,`views`)
) ENGINE=InnoDB;

INSERT INTO media_views (companyid, channel, `date`, views)
     VALUES (1, 0,  '2015-10-01', 15),
            (1, 0,  '2015-10-01', 25),
            (1, 0,  '2015-10-01', 541),
            (1, 5,  '2015-10-01', 2),
            (1, 8,  '2015-10-01', 53),
            (1, 91, '2015-10-01', 8571),
            (3, 12, '2015-10-01', 11),
            (1, 0,  '2015-10-02', 1),
            (1, 0,  '2015-10-02', 13),
            (1, 5,  '2015-10-02', 17),
            (1, 91, '2015-10-03', 37),
            (3, 0,  '2015-10-03', 73);

现在的问题是我有“重复”错误，我有与日期匹配但视图不同的记录。只有视图的最大值才是真正有用的。所以我可以通过执行以下操作来解决这个问题。

SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1;

这清楚地为我提供了以下内容：

+-----------+---------+------------+------------+
| companyid | channel | date       | MAX(views) |
+-----------+---------+------------+------------+
| 1         | 0       | 2015-10-01 | 541        |
| 1         | 0       | 2015-10-02 | 13         |
| 1         | 5       | 2015-10-01 | 2          |
| 1         | 5       | 2015-10-02 | 17         |
| 1         | 8       | 2015-10-01 | 53         |
| 1         | 91      | 2015-10-01 | 8571       |
| 1         | 91      | 2015-10-03 | 37         |
| 3         | 0       | 2015-10-03 | 73         |
| 3         | 12      | 2015-10-01 | 11         |
+-----------+---------+------------+------------+
9 rows in set (0.11 sec)

这很好，但我如何返回并删除重复且小于 max(views) 值的行？

场景如下，前段时间同事不小心跑了一个类似的查询

 DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = '';

这是app_i18n的表结构

CREATE TABLE app_i18n (
     `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
     `category` INTEGER(11) unsigned NOT NULL,
     `keyword`  varchar(50) DEFAULT NULL,
     `language` varchar(2) NOT NULL DEFAULT 'en',
     `translation` text NOT NULL,
     `disabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`category`, `keyword`, `language`, `disabled`)
) ENGINE=InnoDB;

由此产生的问题是，其中一些“翻译”虽然是空的，但存在是有原因的。所以恢复是为了。从数据库的旧副本中，他恢复了所有为空或空且未禁用的行。

未知数量的其他开发人员迅速投入“翻译”以解决他们遇到的问题，使问题变得更加复杂。所以我认为解决方案是

SELECT id 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

该查询会产生我想要的答案吗？即所有具有重复类别的记录，具有重复的相同语言的关键字？

背景资料：

我有一个包含上传信息的表格，每天我都会收到越来越多的上传，但我运行的大部分查询都围绕着过去 12 个月内收集的信息；它经常使用更严格的时间范围，并且查询仅限于过去 30 天内收集的信息

该系统从 2004 年开始存在，当时有 400 个上传，今天有 2+ 百万

表结构：

CREATE TABLE `data_mediagallery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `contenttype` char(40) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `injector` char(40) NOT NULL DEFAULT '',
  `hits` int(11) DEFAULT '0',
  `message` longtext,
  `date` datetime DEFAULT NULL,
  `title` varchar(80) NOT NULL DEFAULT '',
  `tags` varchar(255) NOT NULL DEFAULT '',
  `metadata` blob,
  `location` char(8) NOT NULL DEFAULT '',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `upload` datetime DEFAULT NULL,
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `width` int(10) unsigned NOT NULL DEFAULT '0',
  `height` int(10) unsigned NOT NULL DEFAULT '0',
  `offensive` int(10) unsigned NOT NULL DEFAULT '0',
  `sourcelocation` char(8) NOT NULL DEFAULT '',
  `autoblog` tinyint(1) NOT NULL DEFAULT '0',
  `extension` char(10) NOT NULL DEFAULT '',
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `conversiontime` float NOT NULL DEFAULT '0',
  `converttime` datetime DEFAULT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `vhost` int(10) unsigned NOT NULL DEFAULT '0',
  `channel` int(10) unsigned NOT NULL DEFAULT '0',
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ofilesize` bigint(20) NOT NULL DEFAULT '0',
  `moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` decimal(8,6) DEFAULT NULL,
  `votecount` int(10) unsigned NOT NULL DEFAULT '0',
  `url` varchar(150) NOT NULL DEFAULT '',
  `geo_latitude` double DEFAULT NULL,
  `geo_longitude` double DEFAULT NULL,
  `length` decimal(8,2) DEFAULT '0.00',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `language` char(2) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `context` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `externalid` varchar(255) DEFAULT NULL,
  `originalsaved` bit(1) NOT NULL DEFAULT b'1',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `commentcount` int(11) NOT NULL DEFAULT '0',
  `approvedcomments` int(11) NOT NULL DEFAULT '0',
  `notdeniedcomments` int(11) NOT NULL DEFAULT '0',
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `channelleft` int(10) unsigned NOT NULL DEFAULT '0',
  `originalLocation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `upload` (`upload`),
  KEY `vhostupload` (`vhost`,`upload`),
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
  KEY `externalid` (`externalid`),
  KEY `externalcomments`.
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`)
) ENGINE=InnoDB;

问题

有没有一种方法可以对最有意义的表进行分区？分区甚至有意义吗？如果做分区，新数据怎么处理？

给定以下类型的数据结构。

    ID NAME TREELEFT TREERIGHT 父母
    1 XYZ 1 2 0
    2 美国广播公司 3 6 0
    3 防御力 4 5 2
    4 希杰 7 10 0
    5 荷航 8 9 4
    6 空操作 11 16 0
    7 QRS 12 15 6
    8 TUV 13 14 7

该方法或多或少与嵌套集模型相同，只是我包含了一个父项，只是为了让我的生活更轻松一些。( https://en.wikipedia.org/wiki/Nested_set_model#Example ) 我需要想出一种检测差距的方法。例如，如果 ID=6 有不同的树权，比如 17。技术上会有 1 的差距。

目前，我已经在一系列不同的查询中完成了这项工作。

一个检测 treeright 是否不超过 2n，另一个检测 treeright 是否总是 > than treeleft。另一对确保没有 treelefts 或 treerights 彼此相同。

但是，如果我可以添加检查以确保树没有间隙，我确信其他几个测试将变得多余。谁能想到一个好方法来做到这一点。很长一段时间以来，我一直在为这个问题绞尽脑汁。

给定下表：

CREATE TABLE `longdescs` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  `bug_when` datetime NOT NULL,
  `work_time` decimal(7,2) NOT NULL DEFAULT '0.00',
  `thetext` mediumtext NOT NULL,
  `isprivate` tinyint(4) NOT NULL DEFAULT '0',
  `already_wrapped` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `longdescs_bug_id_idx` (`bug_id`),
  KEY `longdescs_who_idx` (`who`,`bug_id`),
  KEY `longdescs_bug_when_idx` (`bug_when`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `fk_longdescs_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_longdescs_who_profiles_userid` FOREIGN KEY (`who`) REFERENCES `profiles` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82727 DEFAULT CHARSET=utf8

CREATE TABLE `trace` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `comment_id` mediumint(9) NOT NULL,
  `type` varchar(255) NOT NULL,
  `short_hash` char(22) DEFAULT NULL,
  `stack_hash` char(22) DEFAULT NULL,
  `trace_text` mediumtext NOT NULL,
  `quality` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trace_comment_id_idx` (`comment_id`),
  KEY `trace_short_hash_idx` (`short_hash`),
  KEY `trace_stack_hash_idx` (`stack_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

ALTER TABLE trace ADD CONSTRAINT `fk_trace_comment_id_longdescs_comment_id` FOREIGN KEY (`comment_id`) REFERENCES `longdescs`(`comment_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)

MariaDB [bugs4]> 显示警告；

| Level  | Code | Message |
| Warning |  150 | Create table 'bugs4/#sql-4bb_6f1d' with foreign key     constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)                                                                                                                       |

知道为什么我会收到这个错误吗？仅供参考，我查了一下，它似乎是一个错误，但现在不是了。

http://bugs.mysql.com/bug.php?id=16290

鉴于位字段只是数据的二进制表示，需要以稍微“奇怪”的方式进行查询。

使用位字段作为布尔值是否真的提供了任何好处？从我所见，这似乎表明空间是唯一真正的好处。

假设 MySQL 5.5 中基于语句的复制和基于行的复制的混合复制。它在主主场景中究竟是如何工作的？

收到查询的master是否执行，然后将语句写入binlog，然后转发到slave上执行？还是 master 只是简单地写下应该更新的内容，然后 slave 拿起更改？

它还指出，在 MySQL 5.5 中，SBR 是默认设置，但在需要时会切换到 RBR。我怎么知道什么时候需要它？有没有办法强制它只对一个查询 UPDATE 或 INSERT 使用 SBR？

是否可以从变量名创建表？例如，我正在尝试编写以下存储过程，但它不会使用 create 语句编译过去。

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR 
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%' 
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid') 
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records)); 

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END

我相信 innodb 有一个读取线程。但是，使用 MariaDB，您可以设置多个读取线程。我不确定推荐值是多少。

问题

• 有人对 MariaDB 和性能调优有任何经验吗？
• 您能想到 MariaDB 中 XtraDB 使用 MySQL 没有使用的任何特性吗？
• 我可以使用任何类似的设置来充分利用 XtraDB 和 Maria 的增强功能吗？

一个相对简单的问题，但从我的所有研究中我一直无法找到一个像样的答案。

哪个在技术上更正确，在定义处理程序之后或实际使用光标之前打开光标？

例如，

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

在大多数示例中，上述陈述似乎是标准的，但是可以做到吗

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT x,y,z INTO @X, @Y, @Z FROM TableA WHERE X=1;

  IF (@X >= @Y) THEN

      OPEN cur1;
      OPEN cur2;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF done THEN
          LEAVE read_loop;
        END IF;
        IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
        ELSE
          INSERT INTO test.t3 VALUES (a,c);
        END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  ELSE 
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        IF done THEN
          LEAVE read_loop;
        END IF;
          INSERT INTO test.t3 VALUES (a,b);
      END LOOP;

      CLOSE cur1;
  END IF;
END;

是否可以暂时禁用触发器但仅针对一个表。

例如，我有一个表 TableA 与插入、更新和删除触发器。我也有一个具有相同触发器的表 B，但它们只影响表 A 中的某些列。

我现在有一个使用两个表的更新查询。我知道表 A 中的更新需要触发触发器，但表 B 中的更新绝对不需要触发触发器。所以我想禁用这些触发器，直到更新完成。

这可能吗？我正在使用 MySQL 5.1

[附录]

这是一个触发器表 A 本质上

BEGIN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN  
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
END

以下是表 B 上的触发器

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

这是从表 B 触发的存储过程

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN 

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$

我使用以下语法在 mysql 中创建了一个存储过程。

DROP PROCEDURE IF EXISTS `sp-set_comment_count`;

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - AllCount
   DECLARE AC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END $$
DELIMITER ;

仅供参考，我已经大大简化了存储过程，但我知道它可以正常工作，没有任何问题。

我想要做的是从 usergroup_comments 设置一个触发器，就像这样工作。

DROP TRIGGER IF EXISTS `usergroups_comments_insert` 

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

但是由于某种原因，每次我做 mysql 都会向我抛出一个错误，说明第 4 行存在语法错误并没有帮助。

我梳理了 mysql 文档，发现了一些关于触发器限制的信息，但发现它相当复杂。

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

任何想法都会有所帮助。

我目前在两个主节点之间双向进行 MySQL 复制，一个在本地数据中心，另一个在 Amazon EC2。

一切似乎都在复制方面正常运行，本身没有问题，除了偶尔导致冲突的查询，但这些问题很少见。最近我设置了 mysql-proxy 来尝试对两台服务器进行负载平衡，在本地机器接收到 40 个或更多连接后，负载平衡才真正开始，此时后续的数据库连接被转移到 EC2 机器上。

我们最近注意到的一点是，mysql 代理会通知我们有 41 个连接，然后开始平衡。但是，当我连接到本地计算机并执行操作时，SHOW PROCESSLIST;它可能只提供 30 个连接。

任何人都知道为什么这可能吗？

除此之外，作为发出SHOW PROCESSLIST;命令的结果，我注意到在两台机器上运行的大量查询表明它们已经运行超过 5000 秒。我很确定这些是“僵尸”查询，但是有人知道为什么首先要创建它们吗？

仅供参考我们在最新版本的 ubuntu 和 debian 上运行 mysql 版本 5.1.54。

任何想法都会非常有帮助。

[附录]

事实证明我们没有使用 mysql_pconnect，实际上使用的是 mysqli 库。我仍然无法找出发生这种情况的原因，一旦我发现就会报告。

我在 mysql 中有一个查询如下。

SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

由于各个表的大小，现在执行查询大约需要 13 秒。但是这个查询要快得多（即 2 秒）

SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

我知道这可能会导致行不清晰，但我不确定前端的 php 代码是否已经解决了这种情况。所以我其实是想比较两个结果集的数据，如果不限制的话，这两个结果集的结果都是大约83000行。

任何人都可以想出一种在 SQL 中执行此操作而不是通过 php 脚本执行此操作的方法。我已经用肉眼比较了前 100 行，没有任何问题，但众所周知，只有在第 101 行有一个例外才会把一切都搞砸。所以我需要一个我可以经常执行几次的查询，以查看一个结果集中是否有行不在另一个结果集中。