Mysql

獲取併計算所有符合 LIKE 條件的部落格文章和所有使用者（兩個單獨的查詢）

  • August 27, 2012

我有這個表：

--
-- Table structure for table `default_cmvblog`
--

CREATE TABLE IF NOT EXISTS `default_cmvblog` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `slug` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `category_id` int(11) NOT NULL,
 `intro` text COLLATE utf8_unicode_ci NOT NULL,
 `body` text COLLATE utf8_unicode_ci NOT NULL,
 `keywords` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `author_id` int(11) NOT NULL DEFAULT '0',
 `created_on` date DEFAULT NULL,
 `updated_on` date DEFAULT NULL,
 `comments_enabled` int(1) NOT NULL DEFAULT '1',
 `status` enum('draft','live') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'draft',
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_title` (`title`),
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `default_profiles`
--

CREATE TABLE IF NOT EXISTS `default_profiles` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `created` datetime DEFAULT NULL,
 `updated` datetime DEFAULT NULL,
 `created_by` int(11) DEFAULT NULL,
 `ordering_count` int(11) DEFAULT NULL,
 `user_id` int(11) unsigned NOT NULL,
 `display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `updated_on` int(11) unsigned DEFAULT NULL,
 `phone_1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
 `phone_2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
 `avatar` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=23172 ;

--
-- Dumping data for table `default_profiles`
--

INSERT INTO `default_profiles` (`id`, `created`, `updated`, `created_by`, `ordering_count`, `user_id`, `display_name`, `first_name`, `last_name`, `updated_on`, `phone_1`, `phone_2`, `avatar`) VALUES
(1, NULL, NULL, NULL, NULL, 1, 'ReynierPM', 'Reynier', 'Perez Mira', 1346098402, '04241805609', '04241805609', 1),
(2, '2012-08-13 11:44:29', NULL, 1, 1, 2, 'Demo', 'Demo', 'Demo', NULL, NULL, NULL, NULL),
(3, '2012-08-27 10:09:35', NULL, 1, 2, 3, '86a7d120c', '86a7d120c-FN', '86a7d120c-LN', NULL, NULL, NULL, NULL),
(4, '2012-08-27 10:09:35', NULL, 1, 3, 4, '86a7d48a6', '86a7d48a6-FN', '86a7d48a6-LN', NULL, NULL, NULL, NULL),
(5, '2012-08-27 10:09:35', NULL, 1, 4, 5, '86a7d5989', '86a7d5989-FN', '86a7d5989-LN', NULL, NULL, NULL, NULL),
(6, '2012-08-27 10:09:35', NULL, 1, 5, 6, '86a7d6a45', '86a7d6a45-FN', '86a7d6a45-LN', NULL, NULL, NULL, NULL),
(7, '2012-08-27 10:09:35', NULL, 1, 6, 7, '86a7d7bcd', '86a7d7bcd-FN', '86a7d7bcd-LN', NULL, NULL, NULL, NULL),
(8, '2012-08-27 10:09:35', NULL, 1, 7, 8, '86a7d8db6', '86a7d8db6-FN', '86a7d8db6-LN', NULL, NULL, NULL, NULL),
(9, '2012-08-27 10:09:35', NULL, 1, 8, 9, '86a7d9eb9', '86a7d9eb9-FN', '86a7d9eb9-LN', NULL, NULL, NULL, NULL),
(10, '2012-08-27 10:09:35', NULL, 1, 9, 10, '86a7daf8c', '86a7daf8c-FN', '86a7daf8c-LN', NULL, NULL, NULL, NULL),
(11, '2012-08-27 10:09:35', NULL, 1, 10, 11, '86a7dc0db', '86a7dc0db-FN', '86a7dc0db-LN', NULL, NULL, NULL, NULL),
(12, '2012-08-27 10:09:35', NULL, 1, 11, 12, '86a7dd239', '86a7dd239-FN', '86a7dd239-LN', NULL, NULL, NULL, NULL),
(13, '2012-08-27 10:09:35', NULL, 1, 12, 13, '86a7de330', '86a7de330-FN', '86a7de330-LN', NULL, NULL, NULL, NULL);

--
-- Table structure for table `default_users`
--

CREATE TABLE IF NOT EXISTS `default_users` (
 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `salt` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `group_id` int(11) DEFAULT NULL,
 `ip_address` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
 `active` int(1) DEFAULT NULL,
 `activation_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_on` int(11) NOT NULL,
 `last_login` int(11) NOT NULL,
 `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `forgotten_password_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `remember_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Registered User Information' AUTO_INCREMENT=23172 ;

--
-- Dumping data for table `default_users`
--

INSERT INTO `default_users` (`id`, `email`, `password`, `salt`, `group_id`, `ip_address`, `active`, `activation_code`, `created_on`, `last_login`, `username`, `forgotten_password_code`, `remember_code`) VALUES
(1, 'reynierpm@gmail.com', '87cdcd2c46479ef677689526cc1a71adebfdcde3', '07f7d', 1, '', 1, '', 1344872988, 1346114876, 'admin', NULL, NULL),
(2, 'demo@demo.com', '2ff62db826f753e0f7712578eddcea778a452448', '05e9a0', 7, '127.0.0.1', 1, NULL, 1344890669, 1345251732, 'demo', NULL, NULL),
(3, '86a7d120c@local.com', '8f1e0454cb0e88a3a6ae5657b66c092bb5300e01', '7a2758', 4, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d120c', NULL, NULL),
(4, '86a7d48a6@local.com', '9e760c1f1f1525a035b3947df469c64d2be20843', '8da707', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d48a6', NULL, NULL),
(5, '86a7d5989@local.com', '515ffc6a6c8996ced8699587b7cd192abb361482', '7b01ef', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d5989', NULL, NULL),
(6, '86a7d6a45@local.com', '013bab81603455638e6f2ed6d5f95ac4a57deba7', '5f71d2', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d6a45', NULL, NULL),
(7, '86a7d7bcd@local.com', '56ce2274480ce0a853d33e1ca026f75b4859a281', '9d4234', 2, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d7bcd', NULL, NULL),
(8, '86a7d8db6@local.com', 'f206a3085bf9280adaf7841686c0193edad1194f', '00d026', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d8db6', NULL, NULL),
(9, '86a7d9eb9@local.com', '8ffc88beea7473a7e2dac8493749b4ed1bfe27d1', '66d998', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7d9eb9', NULL, NULL),
(10, '86a7daf8c@local.com', '8ff2ab65ced1752e1fc9a5856721538ee524df31', '312260', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7daf8c', NULL, NULL),
(11, '86a7dc0db@local.com', '13ad8228b71355bc21a2876ba6d684d9826a5aff', 'b3cffb', 5, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7dc0db', NULL, NULL),
(12, '86a7dd239@local.com', '5ba3d3e5cfed7bb9436fc97f5d0efa033742043a', '6bdd6e', 6, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7dd239', NULL, NULL),
(13, '86a7de330@local.com', '5b713fc9883923a75c565ea92abf612862c306c6', 'c4ae49', 3, '127.0.0.1', 1, NULL, 1346094575, 1346094575, '86a7de330', NULL, NULL);

所以我做了這個查詢：

   SELECT 
   COUNT(*) AS num_rows
FROM
   default_cmvblog b
       JOIN
   default_users u ON (b.author_id = u.id)
WHERE
   ((b.title LIKE '%86a7%'
       OR b.body LIKE '%86a7%'
       OR b.keywords LIKE '%86a7%')
       AND b.status = 'live')
       OR (u.email LIKE '%86a7%'
       OR u.username LIKE '%86a7%' AND u.id != 1
       AND u.active = 1)

這應該返回來自 default_cmvblog 的任何記錄，其中標題、內容或關鍵字類似於 86a7 並且狀態也是實時的，對嗎？default_users 應該返回相同的記錄，其中電子郵件或使用者名如 86a7 和 id ！= 1 和 active = 1，對嗎？但是我得到 0 作為 COUNT 為什麼？我的查詢有什麼問題？

提前歡呼和感謝

並且比 Or 綁定得更緊。我想你想在這裡查詢：

OR (u.email LIKE '%86a7%'
       OR u.username LIKE '%86a7%' AND u.id != 1
       AND u.active = 1)

需要多一組括號。此外，您需要將聯接更改為外部聯接以計算兩個表中的記錄。您還需要記住，如果兩者都不存在，則連接將為空。因此，您需要選擇一個非空列來計算它確實存在的表中的記錄並檢查它是否為空。

FROM
default_cmvblog b
   LEFT OUTER JOIN
default_users u ON (b.author_id = u.id)
...
OR ((u.email LIKE '%86a7%'
       OR u.username LIKE '%86a7%') AND (u.id != 1 OR u.id IS NULL)
       AND u.active = 1)

你還需要

如果它應該存在於任何一個中，你想要的是一個完全連接。左連接將為您提供左表，但僅當它也存在於左側時才提供右表。因此，如果您正在尋找右側和左側，您將需要創建一個視圖或使用總和執行相關子查詢以獲得您想要的結果，因為無論如何目標表中都會有兩行。

有關完整的外部連接，請參見此處： http ://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

有關相關子查詢，請參見此處（基本上是您可以像表一樣加入的 select 語句）： https ://stackoverflow.com/questions/530381/mysql-correlated-subquery-in-join-syntax

引用自：https://dba.stackexchange.com/questions/23223

相關問答

Mysql

可以優化此查詢還是已經優化？

  • July 18, 2015
檢視問答
Mysql

這些查詢會返回相同的結果嗎？

  • August 19, 2012
檢視問答
Mysql

需要有關此查詢的幫助獲取我和我所有朋友的所有狀態及其相關評論

  • August 16, 2012
檢視問答
Mysql

獲取配置文件值，其中 message.sender_user_id = profile.user_id

  • July 12, 2012
檢視問答
Mysql

慢查詢記錄一切

  • December 31, 2021
檢視問答
Mysql

使用索引時加快 COUNT(*)

  • July 17, 2020
檢視問答