tag:blogger.com,1999:blog-59465307047421309702024-03-06T16:20:07.273+08:00Jax 的工作紀錄除了在整理學習上的經驗,同時也能幫助其他需要的人Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-5946530704742130970.post-37827215386294788392023-02-21T10:05:00.001+08:002023-02-21T10:05:41.641+08:00[Python] Flask MySQL 連線管理<pre class="py" name="code">
import os
import mysql.connector as sql
from werkzeug.exceptions import HTTPException, BadRequest
from flask import Flask, g, request, json
app = Flask(__name__)
#[ DB 處裡 ]#############################################################
# https://docsxyz.com/zh-hant/wiki/python/connector-python-connectargs
db_config = {
'host' : "localhost",
'user' : "XXXX",
'passwd' : "XXXX",
'db' : 'XXXX',
'use_pure' : True,
'autocommit': True,
'charset' : 'utf8',
}
@app.before_request
def before_request():
# 在 request 前開啟 DB 連線
# g 是 Flask global 在每個 request 有獨立的 context
g.cnt = sql.connect(**db_config)
g.cursor = g.cnt.cursor(dictionary = True)
@app.after_request
def after_request(response):
# 在 request 後結束 DB 連線
cursor = g.get('cursor', None)
if cursor is not None:
# 當 cursor 還有 row 沒有取出,close 會發生錯誤
if cursor.with_rows : cursor.fetchall()
cursor.close()
cnt = g.get('cnt', None)
if cnt is not None:
cnt.close()
return response
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-118903930618998982013-08-07T00:01:00.000+08:002013-08-17T15:01:12.638+08:00[MySQL] 計算漢明距離之前寫了用 PHP 計算圖片的 hash code,接著要怎麼去搜尋相似的圖片呢?<br />
<br />
資料庫是一個很好的選擇,首先建立一個簡單的 Table:<br />
<pre class="sql:nogutter:nocontrols" name="code">CREATE TABLE image_hash (
id INT(11) NOT NULL AUTO_INCREMENT,
filepath VARCHAR(256) NOT NULL,
hash BIGINT(20) UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY (id)
) ENGINE=MYISAM;
</pre><br />
<br />
再來要怎麼將 hash code 寫進資料表呢?我們可以利用 b'010101' 的 bit 表示語法將 hash code 64位元字串存進 BIGINT 裡面。<br />
<pre class="sql:nogutter:nocontrols" name="code">INSERT INTO image_hash_info (filepath, hash)
VALUES('/home/jax/12.jpg', b'10100111...');
</pre><br />
<br />
資料已經備齊了,最重要的就是怎麼計算漢明距離了,MySQL 提供了 <code style="color: red; font-weight: bold;">BIT_COUNT()</code> 的函數,可以計算數字中位元 1 的個數,所以剩下的就是 XOR 的運算了。<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
-- -> 4, 3
</pre><br />
<br />
找尋與某一個 hash code 相似的圖片<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM image_hash
WHERE BIT_COUNT(hash ^ b'1010011110...') <= 10
</pre><br />
<br />
參考來源:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html">MySQL 5.0 Bit Functions</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com6tag:blogger.com,1999:blog-5946530704742130970.post-40174182466436538822013-07-05T00:05:00.000+08:002014-10-08T13:34:58.480+08:00[轉載][MySQL] Storage Engines轉載自:<a target="_blank" href="http://twpug.net/docs/mysql-5.1/pluggable-storage.html">Chapter 14. Pluggable Storage Engine Architecture</a><br />
<br />
<h3><a name="pluggable-storage"></a>第14章:插件式儲存引擎體系結構</h3><br />
<b>目錄</b><br />
<dl><dt><a href="#pluggable-storage-introduction">14.1. 前言</a></dt>
<dt><a href="#pluggable-storage-overview">14.2. 概述</a></dt>
<dt><a href="#pluggable-storage-common-layer">14.3. 公共MySQL資料庫伺服器層</a></dt>
<dt><a href="#pluggable-storage-choosing">14.4. 選擇儲存引擎</a></dt>
<dt><a href="#pluggable-storage-assigning">14.5. 將儲存引擎指定給資料表</a></dt>
<dt><a href="#pluggable-storage-transactions">14.6. 儲存引擎和事務</a></dt>
<dt><a href="#pluggable-storage-plugging">14.7. 插入儲存引擎</a></dt>
<dt><a href="#pluggable-storage-unplugging">14.8. 拔出儲存引擎</a></dt>
<dt><a href="#pluggable-storage-security">14.9. 插件式儲存器的安全含義</a></dt>
</dl><br />
<br />
<h3><a name="pluggable-storage-introduction"></a>14.1. 前言</h3><br />
在MySQL 5.1中,MySQL AB引入了新的插件式儲存引擎體系結構,允許將儲存引擎加載到正在運新的MySQL伺服器中。<br />
<br />
本章介紹了插件式儲存引擎體系結構,概要介紹了與MySQL一起提供的各種儲存引擎,介紹了將儲存引擎賦給資料表的方法,以及插入和拔出儲存引擎的方法。<br />
<br />
<br />
<h3><a name="pluggable-storage-overview"></a>14.2. 概述</h3><br />
使用MySQL插件式儲存引擎體系結構,允許資料庫專業人員為特定的應用需求選擇專門的儲存引擎,完全不需要管理任何特殊的應用編碼要求。採用MySQL伺服器體系結構,由於在儲存級別上提供了一致和簡單的應用模型和API,應用程式編程人員和DBA可不再考慮所有的底層實施細節。因此,儘管不同的儲存引擎具有不同的能力,應用程式是與之分離的。<br />
<br />
在下圖中,以圖形方式介紹了MySQL插件式儲存引擎體系結構:<br />
<br />
<b>圖14.1:MySQL插件式儲存引擎的體系結構</b><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzR8uQeROIl7Os5KhyphenhyphensJ44SJf5IHLa5Yv5yqPLElPMOoJeoJ-v6x8X5uLYr_Bt-EeKpwqZ_QLa20LZjqS8sCQScP6wVReZU7nRkwbRa_suVLVuNmFvM_HgCqslbCITffsGNLclieIctTJJ/s1600/pluggable-storage-overview.png" ><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzR8uQeROIl7Os5KhyphenhyphensJ44SJf5IHLa5Yv5yqPLElPMOoJeoJ-v6x8X5uLYr_Bt-EeKpwqZ_QLa20LZjqS8sCQScP6wVReZU7nRkwbRa_suVLVuNmFvM_HgCqslbCITffsGNLclieIctTJJ/s550/pluggable-storage-overview.png" alt="The MySQL pluggable storage engine architecture" style="border:none;" /></a><br />
<br />
插件式儲存引擎體系結構提供了標準的管理和支援服務集合,它們對所有的基本儲存引擎來說是共同的。儲存引擎本身是資料庫伺服器的組件,負責對在物理伺服器層面上維護的基本數據進行實際操作。<br />
<br />
這是一種高效的模塊化體系結構,它為那些希望專注於特定應用需求的人員提供了巨大的便利和益處,這類特殊應用需求包括數據倉儲、事務處理、高可用性情形等,同時還能利用獨立於任何儲存引擎的一組接口和服務。<br />
<br />
應用程式編程人員和DBA通過位於儲存引擎之上的連接器API和服務層來處理MySQL資料庫。如果應用程式的變化需要改變底層儲存引擎,或需要增加1個或多個額外的儲存引擎以支援新的需求,不需要進行大的編碼或程序更改就能實現這類要求。MySQL伺服器體系結構提供了一致和易於使用的API,這類API適用於多種儲存引擎,通過該方式,該結構將應用程式與儲存引擎的底層複雜性隔離開來。<br />
<br />
<br />
<h3><a name="pluggable-storage-common-layer"></a>14.3. 公共MySQL資料庫伺服器層</h3><br />
MySQL插件式儲存引擎是MySQL資料庫伺服器中的組件,負責為資料庫執行實際的數據I/O操作,並能允許和強制執行面向特殊應用需求的特定特性集合。使用特殊儲存引擎的主要優點之一在於,僅需提供特殊應用所需的特性,因此,資料庫中的系統開銷較小,最終結果具有更有效和更高的資料庫性能。這也是MySQL被始終視為具有高性能的原因之一,在行業標準基準方面,它能匹敵或擊敗專有的整體式資料庫。<br />
<br />
從技術角度上看,在儲存引擎中,一些獨特的支援底層結構的組件是什麼呢?一些關鍵差別包括:<br />
<ul><li><strong>並發性</strong>:某些應用程式比其他應用程式具有很多的顆粒級鎖定要求(如行級鎖定)。選擇正確的鎖定策略能夠減少開銷,並有助於整體性能的提升。它還包括對多種能力的支援,如多版本並發性控制或「快照」讀取等。</li>
<li><strong>事務支援</strong>:並非所有的應用程式都需要事務,但對的確需要事務的應用程式來說,有著定義良好的需求,如ACID兼容等。</li>
<li><strong>引用完整性</strong>:通過DDL定義的外部鍵,伺服器需要強制保持關聯資料庫的引用完整性。</li>
<li><strong>物理儲存</strong>:它包括各種各樣的事項,從資料表和索引的總的頁大小,到儲存數據所需的格式,到物理磁盤。</li>
<li><strong>索引支援</strong>:不同的應用程式傾向於採用不同的索引策略,每種儲存引擎通常有自己的編制索引方法,但某些索引方法(如B-tree索引)對幾乎所有的儲存引擎來說是共同的。</li>
<li><strong>內存高速緩衝</strong>:與其他應用程式相比,不同的應用程式對某些內存高速緩衝策略的響應更好,因此,儘管某些內存高速緩衝對所有儲存引擎來說是共同的(如用於用戶連接的高速緩衝,MySQL的高速查詢高速緩衝等),其他高速緩衝策略僅當使用特殊的儲存引擎時才唯一定義。</li>
<li><strong>性能幫助</strong>:包括針對並行操作的多I/O線程,線程並發性,資料庫檢查點,成批插入處理等。</li>
<li><strong>其他目標特性</strong>:可能包括對地理空間操作的支援,對特定數據處理操作的安全限制等。</li>
</ul>每組插件式儲存引擎基本組件均採用了相應的設計,能夠為特定應用提供可選擇的特性集合。從反面角度看,避免使用組件特性集合有助於避免不必要的開銷。因此,顯而易見,應理解特定應用程式的需求集合,並選擇恰當的能大幅度改善系統整體效率和性能的MySQL儲存引擎。<br />
<br />
<br />
<h3><a name="pluggable-storage-choosing"></a>14.4. 選擇儲存引擎</h3><br />
與MySQL一起提供的各種儲存引擎在設計時考慮了不同的使用情況。為了更有效地使用插件式儲存體系結構,最好瞭解各種儲存引擎的優點和缺點。<br />
<br />
在下面的資料表格中,概要介紹了與MySQL一起提供的儲存引擎:<br />
<br />
<b>圖14.2:儲存引擎比較</b><br />
<a href="http://4.bp.blogspot.com/-7D3K5C4e280/UdWbUusCB4I/AAAAAAAANHg/ZVWEXxB1Pwo/s1600/pluggable-storage-choosing.png" ><img src="http://4.bp.blogspot.com/-7D3K5C4e280/UdWbUusCB4I/AAAAAAAANHg/ZVWEXxB1Pwo/s550/pluggable-storage-choosing.png" alt="Storage engine comparison" style="border:none;" /></a><br />
<br />
下述儲存引擎是最常用的:<br />
<ul><li><strong>MyISAM</strong>:預設的MySQL插件式儲存引擎,它是在Web、數據倉儲和其他應用環境下最常使用的儲存引擎之一。注意,通過更改STORAGE_ENGINE配置變數,能夠方便地更改MySQL伺服器的預設儲存引擎。</li>
<li><strong>InnoDB</strong>:用於事務處理應用程式,具有眾多特性,包括ACID事務支援。</li>
<li><strong>BDB</strong>:可替代InnoDB的事務引擎,支援COMMIT、ROLLBACK和其他事務特性。</li>
<li><strong>Memory</strong>:將所有數據保存在RAM中,在需要快速搜尋引用和其他類似數據的環境下,可提供極快的訪問。</li>
<li><strong>Merge</strong>:允許MySQL DBA或開發人員將一系列等同的MyISAM資料表以邏輯方式組合在一起,並作為1個對象引用它們。對於諸如數據倉儲等VLDB環境十分適合。</li>
<li><strong>Archive</strong>:為大量很少引用的歷史、歸檔、或安全審計訊息的儲存和檢索提供了完美的解決方案。</li>
<li><strong>Federated</strong>:能夠將多個分離的MySQL伺服器連結起來,從多個物理伺服器建立一個邏輯資料庫。十分適合於分佈式環境或數據集市環境。</li>
<li><strong>Cluster/NDB</strong>:MySQL的叢集式資料庫引擎,尤其適合於具有高性能搜尋要求的應用程式,這類搜尋需求還要求具有最高的正常工作時間和可用性。</li>
<li><strong>Other</strong>:其他儲存引擎包括CSV(引用由逗號隔開的用作資料庫資料表的檔案),Blackhole(用於臨時禁止對資料庫的應用程式輸入),以及Example引擎(可為快速建立定制的插件式儲存引擎提供幫助)。</li>
</ul>請記住,對於整個伺服器或方案,您並不一定要使用相同的儲存引擎,您可以為方案中的每個資料表使用不同的儲存引擎,這點很重要。<br />
<br />
關於MySQL中所包含儲存引擎的詳細訊息,請參見<a href="#pluggable-storage-choosing" title="14.4. Choosing a Storage Engine">14.4節,「選擇儲存引擎」</a>。<br />
<br />
<a href="http://3.bp.blogspot.com/-vgI7FTEufvY/UdWbUgPX7wI/AAAAAAAANHg/wMkGQfGFUbE/s1600/4w8SqRsqldiagram.png" ><img src="http://3.bp.blogspot.com/-vgI7FTEufvY/UdWbUgPX7wI/AAAAAAAANHg/wMkGQfGFUbE/s550/4w8SqRsqldiagram.png" style="border:none;" /></a><br />
<br />
<br />
<h3><a name="pluggable-storage-assigning"></a>14.5. 將儲存引擎指定給資料表</h3><br />
可以在建立新資料表時指定儲存引擎,或通過使用ALTER TABLE語句指定儲存引擎。<br />
<br />
要想在建立資料表時指定儲存引擎,可使用ENGINE參數:<br />
<pre class="sql:nogutter:nocontrols" name="code">CREATE TABLE engineTest(
id INT
) ENGINE = MyISAM;
</pre><br />
要想更改已有資料表的儲存引擎,可使用ALTER TABLE語句:<br />
<pre class="sql:nogutter:nocontrols" name="code">ALTER TABLE engineTest ENGINE = ARCHIVE;</pre><br />
<br />
<h3><a name="pluggable-storage-transactions"></a>14.6. 儲存引擎和事務</h3><br />
下述儲存引擎支援事務:<br />
<ul><li>InnoDB:通過MVCC支援事務,允許COMMIT、ROLLBACK和保存點。</li>
<li>NDB:通過MVCC支援事務,允許COMMIT和ROLLBACK。</li>
<li>BDB:支援事務,允許COMMIT和ROLLBACK。</li>
</ul><br />
<br />
<h3><a name="pluggable-storage-plugging"></a>14.7. 插入儲存引擎</h3><br />
能夠使用儲存引擎之前,必須使用INSTALL PLUGIN語句將儲存引擎plugin(插件)裝載到mysql。例如,要想加載example引擎,首先應加載ha_example.so模塊:<br />
<pre class="sql:nogutter:nocontrols" name="code">INSTALL PLUGIN ha_example SONAME 'ha_example.so';</pre><br />
檔案.so必須位於MySQL伺服器庫目錄下(典型情況下是installdir/lib)。<br />
<br />
<br />
<h3><a name="pluggable-storage-unplugging"></a>14.8. 拔出儲存引擎</h3><br />
要想拔出儲存引擎,可使用UNINSTALL PLUGIN語句:<br />
<pre class="sql:nogutter:nocontrols" name="code">UNINSTALL PLUGIN ha_example;</pre><br />
如果拔出了正被已有資料表使用的儲存引擎,這些資料表將成為不可訪問的。拔出儲存引擎之前,請確保沒有任何資料表使用該儲存引擎。<br />
<br />
<br />
<h3><a name="pluggable-storage-security"></a>14.9. 插件式儲存器的安全含義</h3><br />
為了安裝插件式儲存引擎,plugin檔案必須位於恰當的MySQL庫目錄下,而且發出INSTALL PLUGIN語句的用戶必須具有SUPER權限。<br />
<br />
<hr />這是MySQL參考手冊的翻譯版本,關於MySQL參考手冊,請訪問<a target="_blank" href="http://dev.mysql.com/doc/mysql/en">dev.mysql.com</a>。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-41852202603771854022013-06-05T22:58:00.000+08:002013-06-10T22:21:55.621+08:00[轉載][MySQL] 修復工具 mysqlcheck轉載自:<a target="_blank" href="http://saplingidea.iteye.com/blog/482055">mysql修复工具mysqlcheck</a><br />
<br />
<pre class="sh" name="code">mysqlcheck -a -c -o -r -m --all-databases -uroot -p
</pre><br />
即可最佳化所有db<br />
參數含意:<br />
<ul><li>-a = Analyse given tables.</li>
<li>-c = Check table for errors</li>
<li>-o = Optimise table</li>
<li>-r = Can fix almost anything except unique keys that aren't unique</li>
<li>-m = --medium-check</li>
</ul><br />
<br />
mysqlcheck 客戶端可以檢查和修復 MyISAM 資料表。它還可以優化和分析資料表。<br />
<br />
mysqlcheck 的功能類似 myisamchk,但其工作不同。主要差別是當 mysqld 服務器在運行時必須使用mysqlcheck,<strong>而 myisamchk 應用於服務器沒有運行時</strong>。使用 <strong>mysqlcheck 的好處是不需要停止服務器來檢查或修復資料表</strong>。使用 <strong>myisamchk 修復失敗是不可逆的</strong>。<br />
<br />
Mysqlcheck 為用戶提供了一種方便的使用 SQL 語句 <strong>CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABLE</strong> 的方式。它確定在要執行的操作中使用使用哪個語句,然後將語句發送到要執行的服務器上。<br />
<br />
<br />
有3種方式來調用mysqlcheck:<br />
shell> mysqlcheck [options] db_name [tbl_name ...]<br />
shell> mysqlcheck [options] --databases db_name ...<br />
shell> mysqlcheck [options] --all-databases<br />
<br />
如果沒有指定任何資料表或使用 --databases 或 --all-databases 選項,則檢查整個數據庫。<br />
<br />
同其它客戶端比較,mysqlcheck 有一個特殊特性。重新命名二進制可以更改檢查資料表的默認行為(--check)。如果你想要一個工具默認可以修復資料表的工具,只需要將 mysqlcheck 重新復制為 mysqlrepair,或者使用一個符號鏈接 mysqlrepair 鏈接 mysqlcheck。如果調用 mysqlrepair,可按照命令修復資料表。<br />
<br />
<br />
下面的名可用來更改 mysqlcheck 的默認行為:<br />
<table class="table_list"><tr><td>mysqlrepair</td><td>默認選項為 --repair</td></tr>
<tr><td>mysqlanalyze</td><td>默認選項為 --analyze</td></tr>
<tr><td>mysqloptimize</td><td>默認選項為 --optimize</td></tr>
</table><br />
<br />
mysqlcheck支持下面的選項:<br />
<dl><dt>-A, --all-databases</dt>
<dd>檢查所有數據庫中的所有資料表。與使用---database選項相同,在命令行中命名所有數據庫。</dd>
<dt>-a, --analyze</dt>
<dd>分析資料表。</dd>
<dt>-1, --all-in-1</dt>
<dd>不是為每個表發出一個語句,而是為命名數據庫中待處理的所有表的每個數據庫執行一個語句。</dd>
<dt>--auto-repair</dt>
<dd>如果某個被檢查的表破壞了,自動修復它。檢查完所有表後自動進行所有需要的修復。</dd>
<dt>--character-sets-dir=name</dt>
<dd>字符集的安裝目錄。參見5.10.1節,"數據和排序用字符集"。</dd>
<dt>-c, --check</dt>
<dd>檢查資料表的錯誤。</dd>
<dt>-C, --check-only-changed</dt>
<dd>只檢查上次檢查以來已經更改的或沒有正確關閉的資料表。</dd>
<dt>--compress</dt>
<dd>壓縮在客戶端和服務器之間發送的所有信息(如果二者均支持壓縮)。</dd>
<dt>-B, --databases</dt>
<dd>處理數據庫中命名的所有資料表。使用該選項,所有字名參量被看作數據庫名,而不是資料表名稱。</dd>
<dt>-#, --debug[=#]</dt>
<dd>寫調試日誌。 debug_options字符串通常為'd:t:o,file_name'。</dd>
<dt>--default-character-set=name</dt>
<dd>使用charsetas默認字符集。參見5.10.1節,"數據和排序用字符集"。</dd>
<dt>-F, --fast</dt>
<dd>只檢查沒有正確關閉的資料表。</dd>
<dt>-f, --force</dt>
<dd>即使出現SQL錯誤也繼續。</dd>
<dt>-e, --extended</dt>
<dd>如果你正使用該選項來檢查資料表,可以確保它們100%地一致,但需要很長的時間。 如果你正使用該選項來修復資料表,則運行擴展修復,不但執行的時間很長,而且還會產生大量的垃圾行!</dd>
<dt>-?, --help</dt>
<dd>顯示幫助消息並退出。</dd>
<dt>-h, --host=name</dt>
<dd>連接給定主機上的MySQL服務器。</dd>
<dt>-m, --medium-check</dt>
<dd>執行比--extended操作更快的檢查。只能發現99.99%的錯誤,在大多數情況下這已經足夠了。</dd>
<dt>-o, --optimize</dt>
<dd>優化資料表。</dd>
<dt>-p, --password[=name]</dt>
<dd>當連接服務器時使用的密碼。如果使用短選項形式(-p),選項和密碼之間不能有空格。 如果在命令行中--password或-p選項後面沒有密碼值,則提示輸入一個密碼。</dd>
<dt>-P, --port=#</dt>
<dd>用於連接的TCP/IP端口號。</dd>
<dt>--protocol=name</dt>
<dd>使用的連接協議。</dd>
<dt>-q, --quick</dt>
<dd>如果你正使用該選項在檢查資料表,它防止掃描行以檢查錯誤鏈接的檢查。這是最快的檢查方法。 如果你正使用該選項在修復資料表,它嘗試只修復索引樹。這是最快的修復方法。</dd>
<dt>-r, --repair</dt>
<dd>執行可以修復大部分問題的修復,只是唯一值不唯一時不能修復。</dd>
<dt>-s, --silent</dt>
<dd>沉默模式。只打印錯誤消息。</dd>
<dt>-S, --socket=name</dt>
<dd>用於連接的套接字文件。</dd>
<dt>--tables</dt>
<dd>覆蓋---database或-B選項。選項後面的所有參量被視為資料表名稱。</dd>
<dt>-u, --user=name</dt>
<dd>當連接服務器時使用的MySQL用戶名。</dd>
<dt>-v, --verbose</dt>
<dd>冗長模式。打印關於各階段程序操作的信息。</dd>
<dt>-V, --version</dt>
<dd>顯示版本信息並退出。</dd> </dl>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-59839398578621841702012-09-05T11:46:00.000+08:002013-06-10T22:24:47.475+08:00[轉載] [MySQL] QueryCache原理轉載自:<a target="_blank" href="http://calos-tw.blogspot.tw/2012/09/mysql-querycache.html">PHP 程式 學習 筆記本 [Mysql]QueryCache原理</a><br />
<br />
<strong>原理 </strong><br />
QueryCache(下面簡稱QC)是根據SQL語句來cache的。一個SQL查詢如果以select開頭,那麼MySQL服務器將嘗試對其使用QC。每個Cache都是以SQL文本作為key來存的。在應用QC之前,SQL文本不會被作任何處理。也就是說,兩個SQL語句,只要相差哪怕是一個字符(例如大小寫不一樣;多一個空格等),那麼這兩個SQL將使用不同的一個CACHE。 <br />
<br />
不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端裡,在發送SQL給服務器之前,會做如下處理: <br />
<ul><li>過濾所有註釋</li>
<li>去掉SQL文本前後的空格,TAB等字符。注意,是文本前面和後面的。中間的不會被去掉。</li>
</ul><br />
<br />
下面的三條SQL裡,因為SELECT大小寫的關係,最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條,區別在於後者有個註釋,在不同客戶端,會有不一樣的結果。所以,保險起見,請儘量不要使用動態的註釋。在PHP的mysql擴展裡,SQL的註釋是不會被去掉的。也就是三條SQL會被存儲在三個不同的緩存裡,<b>雖然它們的結果都是一樣的</b>。 <br />
<div style="color: blue;">SELECT * FROM people where name='surfchen';</div><div style="color: blue;">SELECT * FROM people where /*hey~*/name='surfchen';</div><div style="color: blue;">SELECT * FROM people where name='surfchen';</div><br />
<br />
目前只有select語句會被cache,其他類似show,use的語句則不會被cache。 <br />
<br />
因為QC是如此前端,如此簡單的一個緩存系統,所以如果一個表被更新,那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B,如果表A或者表B的其中一個被更新(update或者delete),這個查詢的QC將會失效。 <br />
<br />
也就是說,如果一個表被頻繁更新,那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QC,可能會加重數據庫的負擔,而不是減輕負擔。我一般的做法是默認打開QC,而對一些涉及頻繁更新的表的SQL語句加上<b style="color: red;">SQL_NO_CACHE</b>關鍵詞來對其禁用CACHE。這樣可以儘可能避免不必要的內存操作,儘可能保持內存的連續性。 <br />
<br />
那些查詢很分散的SQL語句,也不應該使用QC。例如用來查詢用戶和密碼的語句——「select pass from user where name='surfchen'」。這樣的語句,在一個系統裡,很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢,都是不一樣的SQL文本,QC在這裡就幾乎不起作用了,因為緩存的數據幾乎是不會被用到的,它們只會在內存裡佔地方。 <br />
<br />
<br />
<strong>存儲塊 </strong><br />
在本節裡「存儲塊」和「block」是同一個意思 <br />
QC緩存一個查詢結果的時候,一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中,逐塊存儲。當一個存儲塊被填滿之後,一個新的存儲塊將會被創建,並分配內存(allocate)。單個存儲塊的內存分配大小通過<b><span style="color: red;">query_cache_min_res_unit</span></b>參數控制,默認為<b><span style="color: blue;">4KB</span></b>。最後一個存儲塊,如果不能被全部利用,那麼沒使用的內存將會被釋放。如果被緩存的結果很大,那麼會可能會導致分配內存操作太頻繁,系統系能也隨之下降;而如果被緩存的結果都很小,那麼可能會導致內存碎片過多,這些碎片如果太小,就很有可能不能再被分配使用。 <br />
<br />
除了查詢結果需要存儲塊之外,每個SQL文本也需要一個存儲塊,而涉及到的表也需要一個存儲塊(表的存儲塊是所有線程共享的,每個表只需要一個存儲塊)。存儲塊總數量=查詢結果數量*2+涉及的數據庫表數量。也就是說,第一個緩存生成的時候,至少需要三個存儲塊:表信息存儲塊,SQL文本存儲塊,查詢結果存儲塊。而第二個查詢如果用的是同一個表,那麼最少只需要兩個存儲塊:SQL文本存儲塊,查詢結果存儲塊。 <br />
<br />
通過觀察<span style="color: red;">Qcache_queries_in_cache</span>和<span style="color: red;">Qcache_total_blocks</span>可以知道平均每個緩存結果占用的存儲塊。它們的比例如果接近<b style="color: blue;">1:2</b>,則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比Qcache_queries_in_cache多很多,則需要增加query_cache_min_res_unit的大小。 <br />
<br />
<b style="color: red;">Qcache_queries_in_cache</b> * <b style="color: red;">query_cache_min_res_unit</b>(sql文本和表信息所在的block佔用的內存很小,可以忽略)如果遠遠大於<b style="color: red;">query_cache_size-Qcache_free_memory</b>,那麼可以嘗試<b style="color: red;"><span style="color: blue;">減小</span>query_cache_min_res_unit</b>的值。 <br />
<br />
<br />
<strong>調整大小 </strong><br />
如果<b style="color: red;">Qcache_lowmem_prunes</b>增長迅速,意味著很多緩存因為內存不夠而被釋放,而不是因為相關表被更新。嘗試<b style="color: red;"><span style="color: blue;">加大</span>query_cache_size</b>,儘量使Qcache_lowmem_prunes零增長。 <br />
<br />
<br />
<strong>啟動參數</strong> <br />
show variables like 'query_cache%' 可以看到這些信息。 <br />
<b style="color: red;">query_cache_limit</b>:如果單個查詢結果大於這個值,則不Cache <br />
<b style="color: red;">query_cache_size</b>:分配給QC的內存。如果設為0,則相當于禁用QC。要注意QC必須使用大約40KB來存儲它的結構,如果設定小於40KB,則相當于禁用QC。QC存儲的最小單位是1024 byte,所以如果你設定了一個不是1024的倍數的值,這個值會被四捨五入到最接近當前值的等於1024的倍數的值。 <br />
<b style="color: red;">query_cache_type</b>:<br />
<span style="color: blue;">0 完全禁止QC</span>,不受SQL語句控制(另外可能要注意的是,即使這裡禁用,上面一個參數所設定的內存大小還是會被分配);<span style="color: blue;"></span><br />
<span style="color: blue;">1啟用QC,可以在SQL語句使用SQL_NO_CACHE禁用</span>;<span style="color: blue;"></span><br />
<span style="color: blue;">2可以在SQL語句使用SQL_CACHE啟用。</span><br />
<span style="color: blue;"></span> <br />
<b style="color: red;">query_cache_min_res_unit</b>:每次給QC結果分配內存的大小 <br />
<br />
<br />
<strong>狀態</strong> <br />
show status like 'Qcache%' 可以看到這些信息。 <br />
<b style="color: red;">Qcache_free_blocks</b>:當一個表被更新之後,和它相關的cache blocks將被free。但是這個block依然可能存在隊列中,除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用<b style="color: blue;">FLUSH QUERY CACHE</b>語句來清空free blocks。 <br />
<br />
其他幾個狀態變量的意義:<br />
<b style="color: red;">Qcache_free_memory</b> 表示查詢緩存區現在還有多少的可用內存,如果很小,考慮增加query_cache_size <br />
<b style="color: red;">Qcache_hits</b> 表示查詢緩存區的命中個數,也就是直接從查詢緩存區作出響應處理的查詢個數<br />
<b style="color: red;">Qcache_inserts</b> 表示查詢緩存區此前總過緩存過多少條查詢命令的結果<br />
<b style="color: red;">Qcache_lowmem_prunes</b> 表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數<br />
<b style="color: red;">Qcache_not_cached</b> 自mysql進程啟動起,沒有被cache的只讀查詢數量(包括select,show,use,desc等) <br />
<b style="color: red;">Qcache_queries_in_cache</b> 當前被cache的SQL數量 <br />
<b style="color: red;">Qcache_total_blocks</b>:在QC中的blocks數。一個query可能被多個blocks存儲,而這幾個blocks中的最後一個,未用滿的內存將會被釋放掉。例如一個QC結果要佔6KB內存,如果query_cache_min_res_unit是4KB,則最後將會生成3個blocks,第一個block用來存儲sql語句文本,這個不會被統計到query+cache_size裡,第二個block為4KB,第三個block為2KB(先allocate4KB,然後釋放多餘的2KB)。每個表,當第一個和它有關的SQL查詢被CACHE的時候,會使用一個block來存儲表信息。也就是說,block會被用在三處地方:表信息,SQL文本,查詢結果。 <br />
<br />
<b>優化提示:</b>如果Qcache_lowmem_prunes 值比較大,表示查詢緩存區大小設置太小,需要增大。如果Qcache_free_blocks 較多,表示內存碎片較多,需要清理,flush query cache<br />
<br />
根據我看的 《High Performance MySQL》中所述,關於query_cache_min_res_unit大小的調優,書中給出了一個計算公式,可以供調優設置參考:<br />
<b>query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache</b><br />
<br />
還要注意一點的是,FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片,改善內存使用狀況,但不會清理查詢緩存區的內容,這個要和RESET QUERY CACHE相區別,不要混淆,後者才是清除查詢緩存區中<br />
的所有的內容。Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-51102446211992833672012-04-11T21:09:00.001+08:002013-08-06T22:42:04.178+08:00[MySQL] 整數型態的大小<table class="table_list" cellspacing="0" cellpadding="4" border="1"><tr class="header"><th>類型</th><th>Byte</th><th>肯定位數</th><th>最大位數</th><th>最大值</th><th>無符號最大值</th></tr>
<tr><td><b>TINYINT</b></td><td>1</td><td>2</td><td>3</td><td>127</td><td>255</td></tr>
<tr><td><b>SMALLINT</b></td><td>2</td><td>4</td><td>5</td><td>32767</td><td>65535</td></tr>
<tr><td><b>MEDIUMINT</b></td><td>3</td><td>7</td><td>8</td><td>8388607</td><td>16777215</td></tr>
<tr><td><b>INT</b></td><td>4</td><td>9</td><td>10</td><td>2147483647</td><td>4294967296</td></tr>
<tr><td><b>BIGINT</b></td><td>8</td><td>19</td><td>20</td><td>9223372036854775807</td><td>18446744073709551615</td></tr>
</table>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-61697428906592415472012-03-14T14:48:00.003+08:002013-06-10T22:38:24.354+08:00[Ubuntu 11] 安裝 Redmine 與 SVN<strong>安裝 LAMP & Redmine</strong><br />
<pre class="sh" name="code"># 安裝 LAMP
apt-get install apache2 php5 mysql-server php5-mysql libapache2-mod-php5 libapache2-mod-auth-mysql
# 安裝 svn, dav_svn
apt-get install subversion libapache2-svn
# 安裝 redmine
apt-get install redmine redmine-mysql libapache2-mod-fcgid libapache2-mod-passenger
# 可以使用下面的方式重新設定 redmine DB 連結
#dpkg-reconfigure redmine
# 啟用 Apache 套件 php5, headers, expires
a2enmod php5 dav_svn auth_mysql cgid fcgid passenger rewrite ssl setenvif
# 安裝 git, phpmyadmin
apt-get install mercurial git-core phpmyadmin
# 連結 phpmyadmin 設定檔
cd /etc/apache2/conf.d/
ln -s ../../phpmyadmin/apache.conf phpmyadmin.conf
service apache2 restart
</pre><br />
<br />
<strong>安裝 Redmine 套件</strong><br />
<pre class="sh" name="code">gem install pandoc-ruby rdiscount rpeg-markdown bluefeather
cd /usr/share/redmine/vendor/plugins
# 安裝 Code Review 套件
hg clone https://bitbucket.org/haru_iida/redmine_code_review
rake db:migrate_plugins RAILS_ENV=production
# 安裝 Markdown Extra formatter 套件
git clone git://github.com/juno/redmine_markdown_extra_formatter.git
# 安裝 reStructuredText formatting 套件
git clone git://github.com/alphabetum/redmine_restructuredtext_formatter.git
cd redmine_restructuredtext_formatter
git checkout pandoc-ruby
</pre><br />
<br />
<strong>建立SVN庫</strong><br />
<pre class="sh" name="code">mkdir -p /home/repoadmin/repos
svnadmin create /home/repoadmin/repos/team1
svnadmin create /home/repoadmin/repos/team2
chown -R www-data:www-data /home/repoadmin/repos
</pre><br />
<br />
<strong>建立SVN認證時需要的DB使用者(redmine_svn_auth)</strong><br />
<pre class="sql" name="code">CREATE USER 'redmine_svn_auth'@'localhost' IDENTIFIED BY 'redmine_svn_auth';
GRANT SELECT(id, login, hashed_password, status ,type) ON redmine_default.users TO 'redmine_svn_auth'@'localhost';
</pre><br />
<br />
<strong>設定 Apache2 Site</strong> <br />
<pre class="sh" name="code">cd /etc/apache2/sites-available/
cp default redmine_svn
vim redmine_svn
</pre><br />
<pre class="xml" name="code"><VirtualHost *:80>
ServerAdmin webmaster@localhost
DocumentRoot /usr/share/redmine/public
<Directory />
Options FollowSymLinks
AllowOverride None
</Directory>
<Directory /usr/share/redmine/public>
Options ExecCGI FollowSymLinks
AllowOverride None
Order allow,deny
Allow from all
RewriteEngine On
RewriteRule ^$ index.html [QSA]
RewriteRule ^([^.]+)$ $1.html [QSA]
RewriteCond %{REQUEST_FILENAME} !-f [OR]
RewriteCond %{REQUEST_FILENAME} dispatch.fcgi$
RewriteRule ^(.*)$ dispatch.fcgi [QSA,L]
</Directory>
<Location /svn>
DAV svn
SVNParentPath /home/repoadmin/repos
SVNPathAuthz off
AuthBasicAuthoritative Off
AuthUserFile /dev/null
AuthType Basic
AuthName "Subversion Repository"
# auth_mysql help in http://localhost/doc/libapache2-mod-auth-mysql/DIRECTIVES.gz
Auth_MYSQL On
Auth_MySQL_Host localhost
Auth_MYSQL_DB redmine_default
Auth_MYSQL_Username redmine_svn_auth
Auth_MYSQL_Password redmine_svn_auth
Auth_MYSQL_Password_Table users
Auth_MYSQL_Username_Field login
Auth_MYSQL_Password_Field hashed_password
Auth_MySQL_Password_Clause " AND status=1 AND type='User' "
Auth_MYSQL_Empty_Passwords Off
Auth_MYSQL_Encryption_Types SHA1Sum
# Options: Crypt_DES, Crypt_MD5, Crypt, PHP_MD5, SHA1Sum, MySQL, Apache
Require valid-user
</Location>
LogLevel warn
CustomLog /var/log/apache2/redmine_access.log combined
ErrorLog /var/log/apache2/redmine_error.log
</VirtualHost>
</pre><br />
<br />
<strong>重新啟動 Apache</strong> <br />
<pre class="sh" name="code">a2dissite default
a2ensite redmine_svn
service apache2 restart
</pre><br />
<br />
<strong>測試網址</strong> <br />
<ul><li>Redmine: <a target="_blank" href="http://localhost/">http://localhost/</a></li>
<li>SVN: <a target="_blank" href="http://localhost/svn/team1/">http://localhost/svn/team1/</a></li>
</ul><br />
<br />
參考來源:<br />
<a target="_blank" href="http://blog.longwin.com.tw/2011/03/redmine-debian-ubuntu-linux-2011/">將 Redmine 安裝於 Debian、Ubuntu Linux</a><br />
<a target="_blank" href="http://www.redmine.org/projects/redmine/wiki/HowTo_Install_Redmine_in_Ubuntu">Ubuntu 10.04 using Passenger</a><br />
<a target="_blank" href="http://www.pigo.idv.tw/archives/851">svn 使用和 redmine 相同帳號進行認證</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com4tag:blogger.com,1999:blog-5946530704742130970.post-40506982560465812332011-12-16T15:04:00.003+08:002023-02-25T21:41:23.871+08:00Sphinx 增量索引的方法之前有寫過一篇 <a href="http://jax-work-archive.blogspot.com/2009/05/mysql-sphinx.html">MySQL 全文檢索引擎 - Sphinx</a> 的文章,最近又把它拿出來用了,不過當時即時更新索引的問題,如今則找到解決的方法了,透過更新增量索引的方式達到即時更新。<br />
<br />
簡單的說就是利用兩個索引表的合併查詢來做到,一個是完整的索引表,一個是針對當日資料變動的增量索引。<br />
<br />
部分的設定檔如下:<br />
<pre class="cfg" name="code"># ...
source _source_base
{
# 來源-共用的設定
}
source people_full : _source_base
{
sql_query = \
SELECT \
people_profile.id, \
people_profile.main_name \
FROM people_profile
sql_query_killlist = \
SELECT id FROM people_profile \
WHERE update_date >= CURDATE()
}
# 增量索引來源,這邊只會抓出當日變動的資料
# 建議在 update_date 欄位加上 MySQL INDEX
source people_delta : people_full
{
sql_query = \
SELECT \
people_profile.id, \
people_profile.main_name \
FROM people_profile \
WHERE people_profile.update_date >= CURDATE()
}
index _index_base
{
# 索引-共用的設定
}
index people_full : _index_base
{
source = people_full
path = /var/lib/sphinxsearch/data/people_full
}
# 增量索引
index people_delta : _index_base
{
source = people_delta
path = /var/lib/sphinxsearch/data/people_delta
}
# 透過 distributed 類型來合併索引
index people
{
type = distributed
local = people_full
local = people_delta
}
# ...
</pre><br />
<br />
<strong>建立 SphinxSE 表</strong><br />
特別注意在 <strong>CONNECTION</strong> 中所指定索引表為 people。<br />
<pre class="sql" name="code">CREATE TABLE people_sphinx(
id BIGINT UNSIGNED NOT NULL COMMENT '搜尋結果的 Id',
weight INT NOT NULL COMMENT '搜尋結果的權重',
query VARCHAR(3072) NOT NULL COMMENT '搜尋的查詢條件',
INDEX(query)
)ENGINE=SPHINX
CONNECTION="sphinx://localhost:9312/people"
COMMENT='People Sphinx搜尋連接介面';
</pre><br />
<br />
<strong>SQL 的查詢測試</strong><br />
這裡使用 INNER JOIN 方式作查詢,這樣對於刪除資料的變動,就不會出現在查詢結果中。<br />
<pre class="sql" name="code">SELECT A.id, A.main_name, B.weight
FROM people_sphinx B
INNER JOIN people_profile A
USING(id)
WHERE B.query='馬丁尼茲;mode=any;limit=1000'
</pre><br />
<br />
<strong>透過 PHP 更新增量索引</strong><br />
在資料 INSERT 或 UPDATE 時,去呼叫索引更新,這樣在第一時間就可以更新索引。 <br />
<pre class="sql" name="code">shell_exec('sudo indexer --quiet --rotate people_delta 2>&1');
</pre><br />
如果 Server 是 Ubuntu,請記得在 <strong>vim /etc/sudoers</strong> 中賦予 apache 使用 indexer 的權限。<br />
<code>www-data ALL=(root) NOPASSWD: /usr/bin/indexer</code><br />
<br />
<br />
<strong>在 crontab 中加上排程</strong><br />
利用離峰時間來更新完整的索引表,由於刪除資料的變動需要更新完整索引表才有辦法移除。<br />
<code>00 00 * * * indexer --quiet --rotate --all > /dev/null 2>&1</code>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-47356931129022017612011-12-16T13:01:00.003+08:002023-02-25T21:46:11.718+08:00[轉載][MySQL] Replication option binlog_format轉載自:<a target="_blank" href="http://wangwei.cao.blog.163.com/blog/static/1023625262010424113726914/">MYSQL5.1复制参数binlog_format</a><br />
<br />
MySQL 5.1 中,在復制方面的改進就是引進了新的複制技術:基於行的複制。 簡言之,這種新技術就是關注表中發生變化的記錄,而非以前的照抄binlog 模式。 從MySQL 5.1.12 開始,可以用以下三種模式來實現:基於SQL語句的複制(statement-based replication, SBR),基於行的複制(row-based replication, RBR),混合模式複制(mixed-based replication, MBR)。 相應地,binlog的格式也有三種:STATEMENT,ROW,MIXED。 MBR 模式中,SBR 模式是默認的。 <br />
<br />
<br />
在運行時可以動態低改變binlog的格式,除了以下幾種情況: <br />
<ol><li>存儲過程或者觸發器中間 </li>
<li>啟用了NDB </li>
<li>當前會話試用RBR 模式,並且已打開了臨時表 </li>
</ol><br />
<br />
如果binlog採用了MIXED 模式,那麼在以下幾種情況下會自動將binlog的模式由SBR 模式改成RBR 模式。 <br />
<ol><li>當DML語句更新一個NDB表時 </li>
<li>當函數中包含UUID() 時 </li>
<li>2個及以上包含AUTO_INCREMENT 字段的表被更新時 </li>
<li>行任何INSERT DELAYED 語句時 </li>
<li>用UDF 時 </li>
<li>視圖中必須要求使用RBR 時,例如創建視圖是使用了UUID() 函數 </li>
</ol><br />
<br />
設定主從復制模式的方法非常簡單,只要在以前設定複製配置的基礎上,再加一個參數: <br />
<pre class="cfg" name="code">binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
</pre><br />
<br />
當然了,也可以在運行時動態修改binlog的格式。 例如 <br />
<pre class="sh" name="code">mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
</pre><br />
<br />
現在來比較以下SBR 和RBR 2中模式各自的優缺點<br />
<br />
<strong>SBR 的優點:</strong><br />
<ol><li>歷史悠久,技術成熟 </li>
<li>binlog文件較小 </li>
<li>binlog中包含了所有數據庫更改信息,可以據此來審核數據庫的安全等情況 </li>
<li>binlog可以用於實時的還原,而不僅僅用於復制 </li>
<li>主從版本可以不一樣,從服務器版本可以比主服務器版本高 </li>
</ol><br />
<strong>SBR 的缺點:</strong><br />
<ol><li>不是所有的UPDATE語句都能被複製,尤其是包含不確定操作的時候。 </li>
<li>調用具有不確定因素的UDF 時復制也可能出問題 </li>
<li>使用以下函數的語句也無法被複製:<ul><li>LOAD_FILE()</li>
<li>UUID()</li>
<li>USER()</li>
<li>FOUND_ROWS()</li>
<li>SYSDATE() (除非啟動時啟用了--sysdate-is-now 選項)</li>
</ul></li>
<li>INSERT ... SELECT 會產生比RBR 更多的行級鎖 </li>
<li>複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的UPDATE 時,需要比RBR 請求更多的行級鎖 </li>
<li>對於有AUTO_INCREMENT 字段的InnoDB表而言,INSERT 語句會阻塞其他INSERT 語句 </li>
<li>對於一些複雜的語句,在從服務器上的耗資源情況會更嚴重,而RBR 模式下,只會對那個發生變化的記錄產生影響 </li>
<li>存儲函數(不是存儲過程)在被調用的同時也會執行一次NOW() 函數,這個可以說是壞事也可能是好事 </li>
<li>確定了的UDF 也需要在從服務器上執行 </li>
<li>數據表必須幾乎和主服務器保持一致才行,否則可能會導致複製出錯 </li>
<li>執行複雜語句如果出錯的話,會消耗更多資源 </li>
</ol><br />
<br />
<strong>RBR 的優點:</strong><br />
<ol><li>任何情況都可以被複製,這對複制來說是最安全可靠的 </li>
<li>和其他大多數數據庫系統的複制技術一樣 </li>
<li>多數情況下,從服務器上的表如果有主鍵的話,複製就會快了很多 </li>
<li>複製以下幾種語句時的行鎖更少:<ul><li>INSERT ... SELECT</li>
<li>包含AUTO_INCREMENT 字段的INSERT</li>
<li>沒有附帶條件或者並沒有修改很多記錄的UPDATE 或DELETE 語句</li>
</ul></li>
<li>執行INSERT,UPDATE,DELETE 語句時鎖更少 </li>
<li>從服務器上採用多線程來執行複製成為可能 </li>
</ol><br />
<strong>RBR 的缺點:</strong><br />
<ol><li> binlog 大了很多 </li>
<li>複雜的回滾時binlog 中會包含大量的數據 </li>
<li>主服務器上執行UPDATE 語句時,所有發生變化的記錄都會寫到binlog 中,而SBR 只會寫一次,這會導致頻繁發生binlog 的並發寫問題 </li>
<li>UDF 產生的大BLOB 值會導致複製變慢 </li>
<li>無法從binlog 中看到都複製了寫什麼語句 </li>
<li>當在非事務表上執行一段堆積的SQL語句時,最好採用SBR 模式,否則很容易導致主從服務器的數據不一致情況發生 </li>
</ol><br />
<br />
另外,針對系統庫 mysql 裡面的表發生變化時的處理規則如下: <br />
<ol><li>如果是採用INSERT,UPDATE,DELETE 直接操作表的情況,則日誌格式根據binlog_format 的設定而記錄 </li>
<li>如果是採用GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那麼無論如何都採用SBR 模式記錄 </li>
</ol><br />
注:採用RBR 模式後,能解決很多原先出現的主鍵重複問題Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-66619390259319908362011-12-16T12:41:00.003+08:002023-02-26T14:13:52.756+08:00[MySQL] Replication Master to Slave<strong>Master上的設定</strong><br />
<br />
my.cnf<br />
<pre class="cfg" name="code"># 主機ID,不可以重複,範圍 0~4294967295
server-id = 1
# Log 的名稱或路徑,只有名稱會將檔案放在 datadir 下
log_bin = mysql-bin
# Log 的格式{ROW,STATEMENT,MIXED},最低支援版本 5.1.8
binlog_format = MIXED
# 需要同步的資料庫,多個寫多行
binlog_do_db = test
binlog_do_db = mydb
# 針對 InnoDB 且有用 transaction 的設定
innodb_flush_log_at_trx_commit=1
sync_binlog=1
</pre><br />
進入Master mysql<br />
<code>mysql -u root -p</code><br />
<br />
建立同步連接帳號 db_sync,密碼12345,給 slave_host,<br />
<code>mysql> GRANT REPLICATION SLAVE ON *.* TO 'db_sync'</code><code>'slave_host' IDENTIFIED BY '12345';</code><br />
<br />
鎖定資料庫寫入<br />
<code>mysql> FLUSH TABLES WITH READ LOCK;</code><br />
<br />
取得 MASTER 上的 Log File 名稱與 Position 編號,這個編號代表當前的 Log 戳記<br />
<code>mysql> SHOW MASTER STATUS\G;</code><br />
<table><tr><th style="text-align:right;">File:</th><th style="text-align:left;">mysql-bin.000010</th></tr>
<tr><th style="text-align:right;">Position:</th><th style="text-align:left;">106</th></tr>
<tr><td style="text-align:right;">Binlog_Do_DB:</td><td>test,mydb</td></tr>
<tr><td style="text-align:right;">Binlog_Ignore_DB:</td><td></td></tr>
</table><br />
離開 mysql<br />
<code>mysql> quit</code><br />
<br />
匯出 MASTER 上的資料 <br />
<code>mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql</code><br />
<br />
解除鎖定<br />
<code>mysql -u root -p -e UNLOCK TABLES;</code><br />
<br />
<br />
<br />
<strong>Slave上的設定</strong><br />
<br />
my.cnf<br />
<pre class="cfg" name="code"># 主機ID,不可以重複,範圍 0~4294967295
server-id = 2
# 需要同步的資料庫,或指定的資料表
replicate_wild_do_table = test.%
replicate_wild_do_table = mydb.category
replicate_wild_do_table = mydb.blog
# 與 Master 斷線後,重新嘗試連接的時間(sec)
master-connect-retry = 60
</pre><br />
匯入資料<br />
<code>mysql -uroot -p test < test.sql</code><br />
<br />
進入 Slave mysql<br />
<code>mysql -uroot -p</code><br />
<br />
停止跟清除之前的 SLAVE 連接 <br />
<code>mysql> STOP SLAVE; RESET SLAVE;</code><br />
<br />
設定Slave 與 Master 的連接<br />
<pre class="sh" name="code">mysql> CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='db_sync',
MASTER_PASSWORD='12345',
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=106;
</pre><br />
啟動 SLAVE 的連接 <br />
<code>mysql> START SLAVE;</code><br />
<br />
察看連接狀態<br />
<code>mysql> SHOW SLAVE STATUS\G;</code><br />
<table><tr><td style="text-align:right;">Slave_IO_State:</td><td>Connecting to master</td></tr>
<tr><td style="text-align:right;">Master_Host:</td><td>master_host</td></tr>
<tr><td style="text-align:right;">Master_User:</td><td>db_sync</td></tr>
<tr><td style="text-align:right;">Master_Port:</td><td>3306</td></tr>
<tr><td style="text-align:right;">Connect_Retry:</td><td>60</td></tr>
<tr><td style="text-align:right;">Master_Log_File:</td><td>mysql-bin.000011</td></tr>
<tr><td style="text-align:right;">Read_Master_Log_Pos:</td><td>106</td></tr>
<tr><td style="text-align:right;">Relay_Log_File:</td><td>master_host-relay-bin.000011</td></tr>
<tr><td style="text-align:right;">Relay_Log_Pos:</td><td>4</td></tr>
<tr><td style="text-align:right;">Relay_Master_Log_File:</td><td>mysql-bin.000011</td></tr>
<tr><th style="text-align:right;">Slave_IO_Running:</th><th style="text-align: left;">Yes</th></tr>
<tr><th style="text-align:right;">Slave_SQL_Running:</th><th style="text-align: left;">Yes</th></tr>
<tr><td style="text-align:right;">Replicate_Do_DB:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Ignore_DB:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Do_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Ignore_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Wild_Do_Table:</td><td>test.%,mydb.category,mydb.blog</td></tr>
<tr><td style="text-align:right;">Replicate_Wild_Ignore_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Last_Errno:</td><td>0</td></tr>
<tr><td style="text-align:right;">Last_Error:</td><td></td></tr>
<tr><td style="text-align:right;">Skip_Counter:</td><td>0</td></tr>
<tr><td style="text-align:right;">Exec_Master_Log_Pos:</td><td>106</td></tr>
<tr><td style="text-align:right;">Relay_Log_Space:</td><td>106</td></tr>
<tr><td style="text-align:right;">Until_Condition:</td><td>None</td></tr>
<tr><td style="text-align:right;">Until_Log_File:</td></tr>
<tr><td style="text-align:right;">Until_Log_Pos:</td><td>0</td></tr>
<tr><td style="text-align:right;">Master_SSL_Allowed:</td><td>No</td></tr>
<tr><td style="text-align:right;">Master_SSL_CA_File:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_CA_Path:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Cert:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Cipher:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Key:</td><td></td></tr>
<tr><th style="text-align:right;">Seconds_Behind_Master:</th><th style="text-align: left;">0</th></tr>
</table><br />
接著到 Master 上新增一筆資料測試看看吧!<br />
<br />
<br />
參考文章:<br />
<a target="_blank" href="http://www.d5s.cn/archives/95">MYSQL 主从服务器配置</a><br />
<a target="_blank" href="http://www.ooso.net/archives/547">mysql主从同步快速设置</a><br />
<a target="_blank" href="http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/">MySQL 設定 Replication (Master – Slave)</a><br />
<a target="_blank" href="http://blog.chinaunix.net/space.php?uid=220350&do=blog&id=149202">多主一从mysql replication同步表的大胆尝试</a><br />
<a target="_blank" href="http://bbs.chinaunix.net/viewthread.php?tid=3566946">MySQL复制的数据库过滤若干问题</a><br />
<a target="_blank" href="http://wangwei.cao.blog.163.com/blog/static/1023625262010424113726914/">MYSQL5.1复制参数binlog_format</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-84007558425881008432011-11-23T00:38:00.005+08:002013-06-10T22:50:38.283+08:00[MySQL] EXPLAIN 分析報告的意義轉載自:<a target="_blank" href="http://chensh.loxa.edu.tw/php/C_7.php">PHP + MySQL 程式設計研習</a><br />
<br />
<ul><li><b>table</b><br />
表示所引用的表格名稱。</li>
<li><b>type</b><br />
表示查詢時的「聯結類型」(join type),以下依序是「最佳」至「最差」的各種類型:</li>
<ul><li><b>system</b><br />
表格中僅有一列。這是 const 類型的一個特例。</li>
<li><b>const</b><br />
表格中符合條件的只有一列。因為僅有一列,其值在後續的查詢中可被視為常數。</li>
<li><b>eq_ref</b><br />
表示在與其它表格的資料列結合時,此表格只有一列會被讀取。當 join 使用到資料表中的所有索引,並索引是 UNIQUE 或 PRIMARY KEY 時才會被用到。</li>
<li><b>ref</b><br />
表示在與其它表格的資料列結合時,此表格中所有符合的資料列都會被讀出來。這是當 join 只使用到部份鍵值(註),或此鍵非 UNIQUE 或 PRIMARY KEY 時才會用到(依照 join 的條件仍然無法選定單一目標列)。若因此符合的資料列數不多的話,它也算是一種不錯的「聯結類型」。<br />
<small>註:我們指定「A+B」欄位為 index key,但查詢時只用到「A」欄位。</small></li>
<li><b>range</b><br />
表示將在一定範圍內執行搜尋的動作。</li>
<li><b>index</b><br />
與 ALL 相同,但只有 index table 會被瀏覽。這通常比 ALL 快,因為 index table 通常比原始資料表來得小。</li>
<li><b>All</b><br />
表示這項查詢將對整個原始資料表瀏覽一遍,是最不好的類型。</li>
</ul><li><b>possible_keys</b><br />
表示 MySQL 能夠藉由哪些 index 來搜尋目標。</li>
<li><b>key</b><br />
表示 MySQL 實際藉由哪個 index 來搜尋目標。</li>
<li><b>key_len</b><br />
表示 MySQL 實際使用的 key 長度。若 index key 是由兩個欄位以上複合而成的話,您可以在此看見 MySQL 使用了 index 的多少部份。</li>
<li><b>ref</b><br />
表示哪個欄位(或常數)將被用來與 key 一起比對。</li>
<li><b>rows</b><br />
表示 MySQL 粗略估計在查詢的過程中,必須瀏覽的資料列數。</li>
<li><b>Extra</b><br />
顯示 MySQL 在解決這項查詢工作時的一些附加訊息。例如:「where used」表示 where 子句將會限制某些資料列的輸出。</li>
</ul>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-40659303160085745362011-11-23T00:14:00.001+08:002013-06-10T22:50:43.580+08:00[MySQL] TEMPORARY 臨時表最近為了解決一個效率太差的問題而找上這個東西,由於有一個資料表的筆數實在太多了,再加上好幾百個 query 都同時在同一個區段做查詢,剛好可以使用臨時表來處理這個問題,原本查詢需要的時間大概超過一分鐘,現在最多只要 10 秒,刷新後約 2 秒。<br />
<br />
MySQL 的臨時表只會存在在 Session 期間,當 Session 結束後就會自動刪除,不同的 Session 的表名稱並不會衝突,所以就算用一樣的名稱也不會出現錯誤,臨時表只能用在 MEMORY,MyISAM,MERGE,或者InnoDB 引擎上。<br />
<br />
<pre class="sql" name="code">-- 透過 SELECT 建立臨時表
CREATE TEMPORARY TABLE my_temp_table ENGINE=MEMORY
SELECT * FROM my_table WHERE col_1 > 1000
-- 為臨時表建立索引
ALTER TABLE my_temp_table
ADD INDEX (col_1),
ADD INDEX (col_1,col_2)
</pre><br />
參考資料:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html">MySQL CREATE TABLE Syntax</a><br />
<a target="_blank" href="http://homeserver.com.tw/mysql/mysql-%E8%87%A8%E6%99%82%E8%A1%A8temporary-table/">mysql temporary table簡介</a><br />
<a target="_blank" href="http://fcamel-life.blogspot.com/2010/11/mysql-temporary-table-on-disk.html">避免 MySQL 使用 temporary table on disk</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-4539080718719165652011-01-19T01:38:00.007+08:002012-02-12T23:32:57.040+08:00[MySQL] mysqldump & mysql command for UTF-8 database最近在 windows 上匯出/匯入資料庫時遇到 UTF-8 編碼問題,奇怪之前在 linux 上執行從未遇到的說,原來 default-character-set 是 latin1,所以在匯出/匯入時要指定成 UTF-8 就沒問題了。<br />
<br />
匯出資料庫<br />
<pre class="sh" name="code">PATH=C:\wamp\bin\mysql\mysql5.5.8\bin
REM export 'dbname' database
mysqldump -uroot -p1234 --default-character-set=utf8 dbname > dbname.sql
REM other dbname ...
pause
</pre><br />
匯入資料庫<br />
<pre class="sh" name="code">PATH=C:\wamp\bin\mysql\mysql5.5.8\bin
REM import 'dbname' database
mysql -uroot -p1234 -e "DROP DATABASE IF EXISTS `dbname`;"
mysql -uroot -p1234 -e "CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
mysql -uroot -p1234 --default-character-set=utf8 dbname < dbname.sql
REM other dbname ...
pause
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-49885689248752137452010-04-20T14:07:00.005+08:002012-02-12T23:32:57.042+08:00MySQL 在做 JOIN 時對索引關連的小發現當兩個 Table 在做 JOIN 時<br />如果在對應上有其他判斷式<br />便不會使用索引關連<br /><pre class="sql" name="code"><br />SELECT * FROM<br /> a<br />INNER JOIN<br /> b<br />ON a.id=b.id &&(b.num=a.num || b.num=0)<br /></pre><br /><br />當我將判斷式移到 WHERE 上去<br />在 ON 上留下有索引的鍵值對應<br />這樣的效率居然比原本的快兩倍<br /><pre class="sql" name="code"><br />SELECT * FROM<br /> a<br />INNER JOIN<br /> b<br />ON a.id=b.id<br />WHERE b.num=a.num || b.num=0<br /></pre><br /><br />結論就是:<br /><span style="color: rgb(255, 0, 0);">在設定關連邏輯時,最好使用單純的鍵值對應</span>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-48916076035390594262010-03-15T21:28:00.010+08:002013-06-11T22:09:11.321+08:00[PHP] 利用 mysqli 建立 MySQL Trigger<pre class="php" name="code"><?php
$dbAdapter = new mysqli('host', 'user', 'pass', 'db');
$sql = "
DROP TRIGGER IF EXISTS `table_insert_trigger`;
CREATE TRIGGER `table_insert_trigger` BEFORE INSERT ON `table`
FOR EACH ROW
SET NEW.`CreateDate`=NOW();
";
$dbAdapter->multi_query($sql);
</pre><br />
就是這麼簡單,為什麼都沒有人寫文章,害我找好久!囧<br />
<br />
參考文章:<br />
<a target="_blank" href="http://codespatter.com/2008/05/06/how-to-use-triggers-to-track-changes-in-mysql/">How To Use Triggers to Track Changes in MySQL</a><br />
<a target="_blank" href="http://pricetalk.wordpress.com/2008/11/12/mysql-mysql-50-reference-manual-1731-trigger-syntax/" title="Permanent Link to MySQL / PHPMyAdmin Trigger Syntax" rel="bookmark">MySQL / PHPMyAdmin Trigger Syntax</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com2tag:blogger.com,1999:blog-5946530704742130970.post-37259010976133264982009-07-19T00:49:00.003+08:002012-02-12T23:32:57.045+08:00InnoDB 匯入時關閉 Foreign Key 檢查 [MySQL]在設有 Foreign Key 的資料表在寫入時都會檢查資料的正確性<br />可是再匯入原有資料時會因為表順序而產生錯誤問題<br />這時候就必須關閉 Foreign Key 檢查<br /><br />我都會在原始資料的 sql 文件最前面加入這兩行<br /><span style="color: rgb(255, 0, 0); font-weight: bold;">第一:先關閉 Foreign Key 檢查,避免匯入失敗</span><br /><span style="color: rgb(255, 0, 0); font-weight: bold;">第二:確定文件的編碼格式,避免亂碼的問題 </span><br /><pre class="sql" name="code"><br />-- 關閉 Foreign Key 檢查, 0 是關閉,1 是開啟<br />-- 設定只會在這次的連結中生效<br />SET FOREIGN_KEY_CHECKS = 0;<br /><br />-- 指定匯入的編碼方式<br />SET NAMES 'UTF8';<br /></pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-19518222906813465452009-07-09T21:32:00.009+08:002012-02-12T23:32:57.046+08:00(轉載)mysqldump 5.1 資料備份詳細指令 [MySQL]轉載自:<a href="http://twpug.net/docs/mysql-5.1/">MySQL 5.1參考手冊</a><br /><br /><h4>mysqldump - 資料庫備份程式</h4><strong style="color: rgb(255, 0, 0);">mysqldump [options] db_name [tables]</strong><br /><strong style="color: rgb(255, 0, 0);">mysqldump [options] --database DB1 [DB2 DB3...]</strong><br /><strong style="color: rgb(255, 0, 0);">mysqldump [options] --all--database</strong><br />如果沒有指定任何資料表或使用了---database或--all--database選項,則轉儲整個資料庫。<br /><br />要想獲得您的版本的mysqldump支援的選項,執行mysqldump ---help。<br /><br />如果運行mysqldump沒有--quick或--opt選項,mysqldump在轉儲結果前將整個結果集裝入內存。如果轉儲大資料庫可能會出現問題。該選項預設啟用,但可以用--skip-opt禁用。<br /><br />如果使用最新版本的mysqldump程式生成一個轉儲重裝到很舊版本的MySQL伺服器中,不應使用--opt或-e選項。<br /><br /><br /><h4>選項 OPTIONS</h4><dl><dt>--help,-?</dt><dd>顯示幫助消息並退出。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--add-drop--database</dt><dd>在每個CREATE DATABASE語句前新增DROP DATABASE語句。<br /><br /></dd><dt>--add-drop-tables</dt><dd>在每個CREATE TABLE語句前新增DROP TABLE語句。<br /><br /></dd><dt>--add-locking</dt><dd>用LOCK TABLES和UNLOCK TABLES語句引用每個資料表轉儲。重載轉儲檔案時插入得更快。參見7.2.16節,「INSERT語句的速度」。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--all--database,-A</dt><dd>轉儲所有資料庫中的所有資料表。與使用---database選項相同,在命令行中命名所有資料庫。<br /><br /></dd><dt>--allow-keywords</dt><dd>允許建立關鍵字列名。應在每個列名前面加上資料表名前綴。<br /><br /></dd><dt>---comments[={0|1}]</dt><dd>如果設置為 0,禁止轉儲檔案中的其它訊息,例如程式版本、伺服器版本和主機。--skip—comments與---comments=0的結果相同。 預設值為1,即包括額外訊息。<br /><br /></dd><dt>--compact</dt><dd>產生少量輸出。該選項禁用註釋並啟用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking選項。<br /><br /></dd><dt>--compatible=name</dt><dd>產生與其它資料庫系統或舊的MySQL伺服器更兼容的輸出。值可以為ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用幾個值,用逗號將它們隔開。這些值與設置伺服器SQL模式的相應選項有相同的含義。參見5.3.2節,「SQL伺服器模式」。<br /><br />該選項不能保證同其它伺服器之間的相容性。它只啟用那些目前能夠使轉儲輸出更兼容的SQL模式值。例如,--compatible=oracle 不映射Oracle類型或使用Oracle註釋語法的數據類型。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--complete-insert,-c</dt><dd>使用包括列名的完整的INSERT語句。<br /><br /></dd><dt>--compress,-C</dt><dd>壓縮在客戶端和伺服器之間發送的所有訊息(如果二者均支援壓縮)。<br /><br /></dd><dt>--create-option</dt><dd>在CREATE TABLE語句中包括所有MySQL資料表選項。<br /><br /></dd><dt>--database,-B</dt><dd>轉儲幾個資料庫。通常情況,mysqldump將命令行中的第1個名字參量看作資料庫名,後面的名看作資料表名。使用該選項,它將所有名字參量看作資料庫名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name語句包含在每個新資料庫前的輸出中。<br /><br /></dd><dt>--debug[=debug_options],-# [debug_options]</dt><dd>寫調試日誌。debug_options字串通常為'd:t:o,file_name'。<br /><br /></dd><dt>--default-character-set=charset</dt><dd>使用charsetas預設字元編碼。參見5.10.1節,「數據和排序用字元編碼」。如果沒有指定,mysqldump使用utf8。<br /><br /></dd><dt>--delayed-insert</dt><dd>使用INSERT DELAYED語句插入行。<br /><br /></dd><dt>--delete-master-logs</dt><dd>在主複製伺服器上,完成轉儲操作後刪除二進制日誌。該選項自動啟用--master-data。<br /><br /></dd><dt>--disable-keys,-K</dt><dd>對於每個資料表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;語句引用INSERT語句。這樣可以更快地裝載轉儲檔案,因為在插入所有行後建立索引。該選項只適合MyISAM資料表。<br /><br /></dd><dt>--extended-insert,-e</dt><dd>使用包括幾個VALUES列資料表的多行INSERT語法。這樣使轉儲檔案更小,重載檔案時可以加速插入。<br /><br /></dd><dt>--fields-terminated-by=...</dt><dt>--fields-enclosed-by=...</dt><dt>--fields-optionally-enclosed-by=...</dt><dt>--fields-escaped-by=...</dt><dt>--lines-terminated-by=...</dt><dd>這些選項結合-T選項使用,與LOAD DATA INFILE的相應子句有相同的含義。參見13.2.5節,「LOAD DATA INFILE語法」。<br /><br /></dd><dt>--first-slave,-x</dt><dd>不贊成使用,現在重新命名為--lock-all-tables。<br /><br /></dd><dt>--flush-logs,-F</dt><dd>開始轉儲前刷新MySQL伺服器日誌檔案。該選項要求RELOAD權限。請注意如果結合--all--database(或-A)選項使用該選項,根據每個轉儲的資料庫刷新日誌。<br /><br />例外情況是當使用--lock-all-tables或--master-data的時候:在這種情況下,日誌只刷新一次,在所有 資料表被鎖定後刷新。如果您想要同時轉儲和刷新日誌,應使用--flush-logs連同--lock-all-tables或--master-data。<br /><br /></dd><dt>--force,-f</dt><dd>在資料表轉儲過程中,即使出現SQL錯誤也繼續。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--host=host_name,-h host_name</dt><dd>從給定主機的MySQL伺服器轉儲數據。預設主機是localhost。<br /><br /></dd><dt>--hex-blob</dt><dd>使用十六進制符號轉儲二進制字串列(例如,'abc' 變為0x616263)。影響到的列有BINARY、VARBINARY、BLOB。<br /><br /></dd><dt>--lock-all-tables,-x</dt><dd>將資料庫中的所有資料表加鎖。在整體轉儲過程中通過全局讀鎖定來實現。該選項自動關閉--single-transaction和--lock-tables。<br /><br /></dd><dt>--lock-tables,-l</dt><dd>開始轉儲前鎖定所有資料表。用READ LOCAL鎖定資料表以允許並行插入MyISAM資料表。對於事務資料表例如InnoDB和BDB,--single-transaction是一個更好的選項,因為它不根本需要鎖定資料表。<br /><br />請注意當轉儲多個資料庫時,--lock-tables分別為每個資料庫鎖定資料表。因此,該選項不能保證轉儲檔案中的資料表在資料庫之間的邏輯一致性。不同資料庫資料表的轉儲狀態可以完全不同。<br /><br /></dd><dt>--master-data[=value]</dt><dd>該選項將二進制日誌的位置和檔案名寫入到輸出中。該選項要求有RELOAD權限,並且必須啟用二進制日誌。如果該選項值等於1,位置和檔案名被寫入CHANGE MASTER語句形式的轉儲輸出,如果您使用該SQL轉儲主伺服器以設置從伺服器,從伺服器從主伺服器二進制日誌的正確位置開始。如果選項值等於2,CHANGE MASTER語句被寫成SQL註釋。如果value被省略,這是預設動作。<br /><br />--master-data選項啟用--lock-all-tables,除非還指定--single-transaction(在這種情況下,只在剛開始轉儲時短時間獲得全局讀鎖定。又見--single-transaction。在任何一種情況下,日誌相關動作發生在轉儲時。該選項自動關閉--lock-tables。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--no-create-db,-n</dt><dd>該選項禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name語句,如果給出---database或--all--database選項,則包含到輸出中。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--no-create-info,-t</dt><dd>不寫重新建立每個轉儲資料表的CREATE TABLE語句。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--no-data,-d</dt><dd>不寫資料表的任何行訊息。如果您只想轉儲資料表的結構這很有用。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--opt</dt><dd>該選項是速記;等同於指定 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset。<br /><br />它可以給出很快的轉儲操作並產生一個可以很快裝入MySQL伺服器的轉儲檔案。該選項預設開啟,但可以用--skip-opt禁用。要想只禁用確信用-opt啟用的選項,使用--skip形式;例如,--skip-add-drop-tables或--skip-quick。<br /><br /></dd><dt style="color: rgb(255, 0, 0); font-weight: bold;">--password[=password],-p[password]</dt><dd>連接伺服器時使用的密碼。如果您使用短選項形式(-p),不能在選項和密碼之間有一個空格。如果在命令行中,忽略了--password或-p選項後面的 密碼值,將提示您輸入一個。<br /><br /></dd><dt>--port=port_num,-P port_num</dt><dd>用於連接的TCP/IP端口號。<br /><br /></dd><dt>--protocol={TCP | SOCKET | PIPE | MEMORY}</dt><dd>使用的連接協議。<br /><br /></dd><dt>--quick,-q</dt><dd>該選項用於轉儲大的資料表。它強制mysqldump從伺服器一次一行地檢索資料表中的行而不是檢索所有行並在輸出前將它緩存到內存中。<br /><br /></dd><dt>--quote-names,-Q</dt><dd>用『`』字元引用資料庫、資料表和列名。如果伺服器SQL模式包括ANSI_QUOTES選項,用『"』字元引用名。預設啟用該選項。可以用--skip-quote-names禁用,但該選項應跟在其它選項後面,例如可以啟用--quote-names的--compatible。<br /><br /></dd><dt>--result-file=file,-r file</dt><dd>將輸出轉向給定的檔案。該選項應用在Windows中,因為它禁止將新行『\n』字元轉換為『\r\n』回車、返回/新行序列。<br /><br /></dd><dt>--routines,-R</dt><dd>在轉儲的資料庫中轉儲儲存程式(函數和程式)。使用---routines產生的輸出包含CREATE PROCEDURE和CREATE FUNCTION語句以重新建立子程式。但是,這些語句不包括屬性,例如子程式定義者或建立和修改時間戳。這說明當重載子程式時,對它們進行建立時定義者應設置為重載用戶,時間戳等於重載時間。<br /><br />如果您需要建立的子程式使用原來的定義者和時間戳屬性,不使用--routines。相反,使用一個具有mysql資料庫相應權限的MySQL帳號直接轉儲和重載mysql.proc資料表的內容。<br /><br />該選項在MySQL 5.1.2中新增進來。在此之前,儲存程式不轉儲。<br /><br /></dd><dt>--set-charset</dt><dd>將SET NAMES default_character_set加到輸出中。該選項預設啟用。要想禁用SET NAMES語句,使用--skip-set-charset。<br /><br /></dd><dt>--single-transaction</dt><dd>該選項從伺服器轉儲數據之前發出一個BEGIN SQL語句。它只適用於事務資料表,例如InnoDB和BDB,因為然後它將在發出BEGIN而沒有阻塞任何應用程式時轉儲一致的資料庫狀態。<br /><br />當使用該選項時,應記住只有InnoDB資料表能以一致的狀態被轉儲。例如,使用該選項時任何轉儲的MyISAM或HEAP資料表仍然可以更改狀態。<br /><br />--single-transaction選項和--lock-tables選項是互斥的,因為LOCK TABLES會使任何掛起的事務隱含提交。<br /><br />要想轉儲大的資料表,應結合--quick使用該選項。<br /><br /></dd><dt>--socket=path,-S path</dt><dd>當連接localhost(為預設主機)時使用的套接字檔案。<br /><br /></dd><dt>--skip--comments</dt><dd>參見---comments選項的描述。<br /><br /></dd><dt>--tab=path,-T path</dt><dd>產生tab分割的數據檔案。對於每個轉儲的資料表,mysqldump建立一個包含建立資料表的CREATE TABLE語句的tbl_name.sql檔案,和一個包含其數據的tbl_name.txt檔案。選項值為寫入檔案的目錄。<br /><br />預設情況,.txt數據檔案的格式是在列值和每行後面的新行之間使用tab字元。可以使用--fields-xxx和--行--xxx選項明顯指定格式。<br /><br />註釋:該選項只適用於mysqldump與mysqld伺服器在同一台機器上運行時。您必須具有FILE權限,並且伺服器必須有在您指定的目錄中有寫檔案的授權。<br /><br /></dd><dt>--tables</dt><dd>覆蓋---database或-B選項。選項後面的所有參量被看作資料表名。<br /><br /></dd><dt>--triggers</dt><dd>為每個轉儲的資料表轉儲觸發器。該選項預設啟用;用--skip-triggers禁用它。<br /><br /></dd><dt>--tz-utc</dt><dd>在轉儲檔案中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同時區的伺服器之間轉儲和重載。(不使用該選項,TIMESTAMP列在具有本地時區的源伺服器和目的伺服器之間轉儲和重載)。--tz-utc也可以保護由於夏令時帶來的更改。--tz-utc預設啟用。要想禁用它,使用--skip-tz-utc。該選項在MySQL 5.1.2中加入。<br /><br /></dd><dt>--user=user_name,-u user_name</dt><dd>連接伺服器時使用的MySQL帳號。<br /><br /></dd><dt>--verbose,-v</dt><dd>冗長模式。打印出程式操作的詳細訊息。<br /><br /></dd><dt>--version,-V</dt><dd>顯示版本訊息並退出。<br /><br /></dd><dt>--where='where-condition', -w 'where-condition'</dt><dd>只轉儲給定的WHERE條件選擇的記錄。請注意如果條件包含命令解釋符專用空格或字元,一定要將條件引用起來。<br />例如:<br />"--where=user='jimf'"<br /><br />"-wuserid>1"<br /><br />"-wuserid<1"<br /><br /></dd><dt>--xml,-X</dt><dd>將轉儲輸出寫成XML。<br /><br /></dd><dt>--var_name=value</dt><dd>用來選項設置下面的變數:<br /><br />max_allowed_packet<br /><br />客戶端/伺服器之間通信的緩存區的最大大小。最大為1GB。<br />net_buffer_length<br /><br />客戶端/伺服器之間通信的緩存區的初始大小。當建立多行插入語句時(如同使用選項--extended-insert或--opt),mysqldump建立長度達net_buffer_length的行。如果增加該變數,還應確保在MySQL伺服器中的net_buffer_length變數至少這麼大。<br /><br />還可以使用--set-variable=var_name=value或-O var_name=value語法設置變數。然而,現在不贊成使用該語法。<br /></dd></dl><br /><br /><h4>範例</h4><pre class="sh" name="code"><br /># mysqldump最常用於備份一個整個的資料庫:<br />mysqldump --opt db_name > backup-file.sql<br /><br /># 您可以這樣將轉儲檔案讀回到伺服器:<br />mysql `db_name` < `backup-file.sql`<br /># 或者為:<br />mysql -e "source /path-to--backup/backup-file.sql" `db_name`<br /><br /><br /><br /># mysqldump也可用於從一個MySQL伺服器向另一個伺服器複製數據時裝載資料庫:<br />mysqldump --opt `db_name` | mysql --host=`remote_host` -C `db_name`<br /><br /><br /><br /># 可以用一個命令轉儲幾個資料庫:<br />mysqldump ---database `db_name1` [`db_name2` ...] > `my_databases.sql`<br /><br /><br /><br /># 如果您想要轉儲所有資料庫,使用--all--database選項:<br />mysqldump --all-databases > `all_databases.sql`<br /><br /># 如果資料表保存在InnoDB儲存引擎中,mysqldump提供了一種聯機備份的途徑(參見下面的命令)。該備份只需要在開始轉儲時對所有資料表進行全局讀鎖定(使用FLUSH TABLES WITH READ LOCK)。獲得鎖定後,讀取二進制日誌的相應內容並將鎖釋放。因此如果並且只有當發出FLUSH...時正執行一個長的更新語句,MySQL伺服器才停止直到長語句結束,然後轉儲則釋放鎖。因此如果MySQL伺服器只接收到短("短執行時間")的更新語句,即使有大量的語句,也不會注意到鎖期間。<br />mysqldump --all-databases --single-transaction > `all_databases.sql`<br /><br /><br /><br /># 對於點對點恢復(也稱為「前滾」,當您需要恢復舊的備份並重放該備份以後的更改時),循環二進制日誌(參見5.11.3節,「二進制日誌」)或至少知道轉儲對應的二進制日誌內容很有用:<br />mysqldump --all-databases --master-data=2 > `all_databases.sql`<br /># 或<br />mysqldump --all-databases --flush-logs --master-data=2 > `all_databases.sql`<br /><br /># 如果資料表保存在InnoDB儲存引擎中,同時使用--master-data和--single-transaction提供了一個很方便的方式來進行適合點對點恢復的聯機備份。<br /><br /><br /><br /># 單純匯出資料,不匯出任何欄位結構<br />mysqldump -u`username` --opt -c -n -t --skip-triggers `dbname` > `databases.sql`<br /></pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-47902211977700692682009-07-09T16:50:00.011+08:002012-02-12T23:32:57.048+08:00MySQL 加解密函數<pre class="sql" name="code"><br />--[單向加密]<br />-- MD5 演算法<br />MD5(str) <br /><br />-- MySQL 密碼演算法<br />PASSWORD(str)<br /><br />-- Unix crypt 演算法<br />ENCRYPT(str[,salt])<br /><br />-- 安全散列演算法<br />SHA1(str)<br />SHA(str)<br /><br /><br />--[雙向加密]<br />-- MySQL 中的低階加解密演算法,<br />DECODE() -- 解密<br />ENCODE() -- 加密<br /><br />-- 以 Advanced Encryption Standard 演算法加解密<br />AES_DECRYPT(crypt_str,key_str) -- 解密<br />AES_ENCRYPT(str,key_str)) -- 加密<br /><br />-- 以 DES 演算法加解密,需要 SSL 的支援<br />DES_DECRYPT(crypt_str[,key_str]) -- 解密<br />DES_ENCRYPT(str[,{key_num|key_str}]) -- 加密 <br /><br /><br />--[資料壓縮]<br />COMPRESS(string_to_compress) -- 壓縮<br />UNCOMPRESS(string_to_uncompress) -- 解壓縮 <br />UNCOMPRESSED_LENGTH(compressed_string) -- 回傳壓縮前的字串長度 <br /></pre><br /><br />對於 key_str 的設定建議使用 MySQL 變數處理<br /><pre class="sql" name="code"><br />-- 在 MySQL 中設定金鑰變數<br />SELECT @key:='sfdgsjhgjgsdfsg'<br /><br />-- 在查詢資料時可以使用金鑰變數,這樣就可以不用在程式中傳遞金鑰了<br />-- 在程式撰寫時看起來也比較簡潔<br />SELECT <br /> `Id`, <br /> AES_DECRYPT(`Name`,@key)AS`Name`, <br /> AES_DECRYPT(`Email`,@key)AS`Email` <br />FROM `system_uesr` <br /></pre><br /><br /><span style="color: rgb(255, 0, 0);">對於加密過的資料必須存放在二進位( <span style="font-weight:bold;">BLOB</span> )的欄位格式中,詳細的型態格式請察看<a href="http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html">10.5. Data Type Storage Requirements</a></span><br /><br /><table width="300" cellspacing="1" cellpadding="1" border="1"><tr><td width="100">類型</td><td width="200"> 大小(單位:字節)</td></tr><tr><td> TinyBlob</td><td> 最大 255</td></tr><tr><td> Blob</td><td> 最大 65K</td></tr><tr><td> MediumBlob</td><td> 最大 16M</td></tr><tr><td> LongBlob</td><td> 最大 4G</td></tr></table> <br /><br /><br />參考來源:<br /><a href="http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html">MySQL 5.1 Reference Manual :: 11.11.2 Encryption and Compression Functions</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-25996571566729924362009-05-02T22:52:00.013+08:002023-02-26T14:18:47.040+08:00MySQL 全文檢索引擎 - Sphinx<a href="http://www.sphinxsearch.com/" target="_blank">Sphinx 官方網站</a><br />
<ul><li><a href="#sphinx_introduce">介绍</a><br />
</li>
<li><a href="#sphinx_emphasis">重點須知</a><br />
</li>
<li><a href="#sphinx_setup">安裝 Sphinx 及 Mysql+SphinxSE</a><br />
</li>
<li><a href="#sphinx_sample">範例環境</a><br />
</li>
<li><a href="#sphinx_conf_construct">sphinx.conf 概述</a><br />
</li>
<li><a href="#sphinx_conf_deploy">sphinx.conf 配置詳解</a><br />
</li>
<li><a href="#sphinx_start">啟動搜尋引擎</a><br />
</li>
<li><a href="#sphinx_software">主要程式及運作方式</a><br />
</li>
<li><a href="#sphinx_command">命令列參數說明</a><br />
</li>
<li><a href="#sphinx_abstract">摘要使用</a><br />
</li>
<li><a href="#sphinxse_matching">搜尋的匹配模式</a><br />
</li>
<li><a href="#sphinxse_table">建立 SphinxSE 介面資料表</a><br />
</li>
<li><a href="#sphinxse_use">SphinxSE 的使用方式</a><br />
</li>
<li><a href="#sphinxse_reference">參考來源</a></li>
</ul><br />
<br />
<br />
<h4><a name="sphinx_introduce"></a>介绍</h4><br />
<blockquote>引用自:<a href="http://dev.cgfinal.com/sphinx/sphinx.html" target="_blank">Sphinx速成指南</a><br />
<p>Sphinx 是一個基於SQL的全文檢索引擎,可以結合MySQL,PostgreSQL做全文搜索,它可以提供比數據庫本身更專業的搜索功能,使得應用程序更容易實現專業化的全文檢索。 Sphinx特別為一些腳本語言設計搜索API接口,如PHP,Python,Perl,Ruby等,同時為MySQL也設計了一個存儲引擎插件。</p><br />
Sphinx的特性:<br />
<ul><li>高速索引(在新款CPU上,近10 MB/秒);<br />
</li>
<li>高速搜索(2-4G的文本量中平均查詢速度不到0.1秒);<br />
</li>
<li>高可用性(單CPU上最大可支持100 GB的文本,100M文檔);<br />
</li>
<li>提供良好的相關性排名<br />
</li>
<li>支持分佈式搜索;<br />
</li>
<li>提供文檔摘要生成;<br />
</li>
<li>提供從MySQL內部的插件式存儲引擎上搜索<br />
</li>
<li>supports boolean, phrase, and word proximity queries;<br />
</li>
<li>支持每個文檔多個全文檢索域(默認最大32個);<br />
</li>
<li>支持每個文檔多屬性;<br />
</li>
<li>支持斷詞;<br />
</li>
<li>支持單字節編碼與UTF-8編碼;<br />
</li>
<li>supports English stemming, Russian stemming, and Soundex for morphology;<br />
</li>
<li>支持MySQL(MyISAM和InnoDB表都支持);<br />
</li>
<li>支持PostgreSQL.</li>
</ul><br />
</blockquote><br />
<br />
<br />
<h4><a name="sphinx_emphasis"></a>重點須知</h4>在你決定是否採用此搜尋引擎前,先瞭解他的應用方式:<br />
<ul style="font-weight: bold; color: rgb(255, 0, 0);"><li>提供多種查詢方式與權重計算,具有 BM25 關鍵字計算,權重計算方式無法變更,但能作外部索引加權計算。<br />
</li>
<li>非網頁爬蟲的模式,是直接對 DataBase 或 XML 作數據索引。<br />
</li>
<li>沒有點擊率的計算,需要自行處理。<br />
</li>
<li>搜尋結果不包含原始資料,需要自行處理。</li>
</ul><br />
<br />
<br />
<h4><a name="sphinx_setup"></a>安裝 Sphinx 及 Mysql+SphinxSE</h4><pre class="sh" name="code"># [安裝 Sphinx 及 Mysql+SphinxSE]
#
# 下載 Sphinx 及 Mysql
wget http://lxr.mysql.com/archives/mysql-5.1/mysql-5.1.31.tar.gz
wget http://www.sphinxsearch.com/downloads/sphinx-0.9.8.1.tar.gz
# 解壓縮 Sphinx 及 Mysql
tar zxvf mysql-5.1.31.tar.gz
tar zxvf sphinx-0.9.8.1.tar.gz
# 安裝 Sphinx
cd sphinx-0.9.8.1/
./configure
make -j$(grep processor /proc/cpuinfo |wc -l)
make install
# 複製 SphinxSE Engine 至 Mysql
cd ..
cp -R ./sphinx-0.9.8.1/mysqlse ./mysql-5.1.31/storage/sphinx
# 安裝 Mysql
# 必要函式庫 automake autoconf libtool libncurses5-dev bison
cd mysql-5.1.31/
sh BUILD/autorun.sh
./configure --with-plugins=sphinx
make
make install
</pre><br />
<br />
<br />
<h4><a name="sphinx_sample"></a>範例環境</h4>這裡用一個簡單的 Blog 範例來模擬接下來的設定<br />
主要由文章和回應的架構來說明一些主要的觀念<br />
<pre class="sql" name="code">-- 資料庫: `sphinx_test`
-- 建立日期: 2009-3-30
-- 設計版本: 1.0
--
SET NAMES 'UTF8';
DROP DATABASE IF EXISTS `sphinx_test`;
CREATE DATABASE `sphinx_test` DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci;
USE `sphinx_test`;
-- @ blog_texts(Blog文章)
CREATE TABLE `blog_texts` (
`BlogId` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`Title` CHAR(80) NOT NULL COMMENT '標題',
`Text` TEXT NOT NULL COMMENT '文章內容',
`InsertDate` DATETIME NOT NULL COMMENT '建立日期',
PRIMARY KEY(`BlogId`)
) ENGINE = INNODB COMMENT = '故事文章';
-- -----------------------------------------------------------------
-- @ blog_comments(Blog回應)
CREATE TABLE `blog_comments` (
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`BlogId` INT UNSIGNED NOT NULL COMMENT '文章 ID',
`Text` TEXT NOT NULL COMMENT '回應內容',
`UpdateDate` DATETIME NOT NULL COMMENT '更新時間',
PRIMARY KEY(`Id`),
INDEX(`BlogId`),
FOREIGN KEY(`BlogId`)REFERENCES blog_texts(`BlogId`)ON DELETE CASCADE
) ENGINE = INNODB COMMENT = 'Blog 回應';
</pre><br />
<br />
<br />
<h4><a name="sphinx_conf_construct"></a>sphinx.conf 概述</h4>設定檔的主要架構由四個區塊組成:<br />
<ul><li>定義資料來源的形式<br />
</li>
<li>定義索引及分詞方式<br />
</li>
<li>配置 indexer 所使用的 memory 及 IO存取設定<br />
</li>
<li>配置 searchd 的服務端口及查詢數量等項關設定</li>
</ul><br />
<pre class="cfg" name="code">source 來源名稱1{
# 資料來源設定
}
index 索引名稱1{
source=來源名稱1
# 索引設定
}
source 來源名稱2{
# 資料來源設定
}
index 索引名稱2{
source = 來源名稱2
# 索引設定
}
indexer{
# indexer 配置選項
}
searchd{
# searchd 配置選項
}
</pre><br />
<br />
<br />
<h4><a name="sphinx_conf_deploy"></a>sphinx.conf 配置詳解</h4>預設的 conf 位址: /usr/local/etc/sphinx.conf <br />
<pre class="cfg" name="code">#
# Sphinx configuration file sample
#
# WARNING! While this sample file mentions all available options,
# it contains (very) short helper descriptions only. Please refer to
# doc/sphinx.html for details.
#
###############################################################
## data source definition
###############################################################
source s_tit
{
# 資料來源的類型。必要設定項,沒有預設值。
# 已知類型 mysql , pgsql , xmlpipe , xmlpipe2。
type = mysql
# SQL 主機連接的 IP。必要設定項,沒有預設值。
# 僅適用於SQL數據源(mysql,pgsql)。
sql_host = localhost
# SQL 主機連接的 port 選擇性設定,預設 mysql(3306),及 pgsql(5432)。
# 僅適用於SQL數據源(mysql,pgsql)。
# 這個設定必須依附在 sql_host 下。
sql_port = 3306 # optional, default is 3306
# 資料庫用戶名稱,必要設定項,沒有預設值。
sql_user = root
# 資料庫用戶密碼,必要設定項,沒有預設值。
sql_pass = 0000
# 資料庫名稱,必要設定項,沒有預設值。
sql_db = sphinx_test
# 資料庫 local 端連結介面,選擇性設定,預設為空。
#sql_sock = /tmp/mysql.sock
# MySQL 傳輸壓縮,可將索引建立時的傳輸量,預設為 0(不壓縮)。
#mysql_connect_flags = 32
# 預先查詢。選擇性,可重複,預設是空。僅適用於SQL數據源(mysql,pgsql)。
# 可用來做索引前的初始設定,如編碼設定、緩衝區大小、是否快取暫存
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
# 主要文件擷取查詢。必要設定項,沒有預設值。僅適用於SQL數據源(mysql,pgsql)。
# 查詢指令允許使用 JOIN 及子查詢,對於資料表不能使用 AS,但資料欄位可以
sql_query = SELECT `BlogId`,`Title`,`InsertDate` FROM `blog_texts`
# 設定過濾條目,被設定的欄位將不會列入索引的範圍,之後可以作為查詢時的過濾條件,0.9.8版支援下列類型。
sql_attr_timestamp = InsertDate
#sql_attr_uint = group_id
#sql_attr_bool = is_deleted # will be packed to 1 bit
#sql_attr_str2ordinal = author_name
#sql_attr_float = long_radians
#sql_attr_multi = uint tag from ranged-query; \
# SELECT id, tag FROM tags WHERE id>=$start AND id<=$end; \
# SELECT MIN(id), MAX(id) FROM tags
# 與 sql_query_pre 的功能相同,但用於索引結束後呼叫的指令
#sql_query_post = DROP TABLE my_tmp_table
#sql_query_info =SELECT * FROM `blog_texts` WHERE `BlogId`=$id
}
#------------------------------------------------------------------------
source s_txt
{
type = mysql
sql_host = localhost
sql_user = root
sql_pass = 0000
sql_db = sphinx_test
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query = SELECT `BlogId`,`Text`,`InsertDate` FROM `blog_texts`
sql_attr_timestamp = InsertDate
sql_ranged_throttle = 0
sql_query_info =SELECT * FROM `blog_texts` WHERE `BlogId`=$id
}
#------------------------------------------------------------------------
source s_comment
{
type = mysql
sql_host = localhost
sql_user = root
sql_pass = 0000
sql_db = sphinx_test
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query_pre = SET group_concat_max_len=1048576
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query = \
SELECT \
`blog_comments`.`BlogId`, \
GROUP_CONCAT(`blog_comments`.`Text`)AS`CommentText`, \
COUNT(`blog_comments`.`BlogId`)AS`CommentNum`, \
`blog_texts`.`InsertDate` \
FROM \
`blog_texts` \
INNER JOIN \
`blog_comments` \
ON `blog_texts`.`BlogId`=`blog_comments`.`BlogId` \
GROUP BY `blog_comments`.`BlogId` \
sql_attr_uint = CommentNum
sql_attr_timestamp = InsertDate
sql_ranged_throttle = 0
sql_query_info =SELECT * FROM `blog_texts` WHERE `BlogId`=$id
}
###############################################################
### index definition
###############################################################
index s_tit
{
# 索引類型。預設為空(索引為簡單本地索引)。設定值有空字串或 "distributed"
#type = distributed
# 指定索引的來源,必須是上面的來源名稱
source = s_tit
# 索引記錄存放的目錄
path = /var/data/s_tit
# 文件屬性值存儲模式,預設值為 extern,可設定的屬性有 'none', 'extern' 及 'inline'.
docinfo = extern
# 鎖定記憶體緩衝區段。預設為 0(不鎖定)
mlock = 0
# 適用的形態前置處理器名單。預設為空(不應用任何前處理器)。
# 可設定的屬性有 'none', 'stem_en', 'stem_ru', 'stem_enru', 'soundex', and 'metaphone'.
# 這段在中文情況下不能使用,否則會導致無法啟動 searchd 服務。
morphology = none
# 停用詞文件清單(以空白分隔),預設為空。
# 在清單中的 word 將不會加到索引庫裡。
#stopwords = /usr/local/sphinx/data/stopwords.txt
# 詞形字典,預設為空。可以設定 word 之間的關連性。
# wordforms.txt
# walks > walk
# walked > walk
# walking > walk
#
#wordforms = /usr/local/sphinx/data/wordforms.txt
# 保留字例外文件,預設為空。
# exceptions.txt
# MS Windows => ms windows
# Microsoft Windows => ms windows
# C++ => cplusplus
# c++ => cplusplus
# C plus plus => cplusplus
#
#exceptions = /usr/local/sphinx/data/exceptions.txt
# exceptions 與 wordforms 的區別
# *exceptions 是區分大小寫的, wordforms沒有;
# *exceptions 允許檢測序列標記, wordforms 只能處理單一的 word;
# *exceptions 可以使用 charset_table 中沒有的特殊符號,wordforms 完全遵從 charset_table 中的字符;
# *exceptions 在大字典上性能會下降,wordforms 則對百萬級的條目應對自如;
# 最短的關鍵字長度
min_word_len = 1
# 編碼格式
charset_type = utf-8
# 指定 UTF-8 的編碼表
charset_table = U+FF10..U+FF19->0..9,0..9,U+FF41..U+FF5A->a..z,\
U+FF21..U+FF3A->a..z,A..Z->a..z,a..z,U+0149,U+017F,U+0138,U+00DF,\
U+00FF,U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6,\
U+00D8..U+00DE->U+00F8..U+00FE,U+00F8..U+00FE,U+0100->U+0101,\
U+0101,U+0102->U+0103,U+0103,U+0104->U+0105,U+0105,U+0106->U+0107,\
U+0107,U+0108->U+0109,U+0109,U+010A->U+010B,U+010B,U+010C->U+010D,\
U+010D,U+010E->U+010F,U+010F,U+0110->U+0111,U+0111,U+0112->U+0113,\
U+0113,U+0114->U+0115,U+0115,U+0116->U+0117,U+0117,U+0118->U+0119,\
U+0119,U+011A->U+011B,U+011B,U+011C->U+011D,U+011D,U+011E->U+011F,\
U+011F,U+0130->U+0131,U+0131,U+0132->U+0133,U+0133,U+0134->U+0135,\
U+0135,U+0136->U+0137,U+0137,U+0139->U+013A,U+013A,U+013B->U+013C,\
U+013C,U+013D->U+013E,U+013E,U+013F->U+0140,U+0140,U+0141->U+0142,\
U+0142,U+0143->U+0144,U+0144,U+0145->U+0146,U+0146,U+0147->U+0148,\
U+0148,U+014A->U+014B,U+014B,U+014C->U+014D,U+014D,U+014E->U+014F,\
U+014F,U+0150->U+0151,U+0151,U+0152->U+0153,U+0153,U+0154->U+0155,\
U+0155,U+0156->U+0157,U+0157,U+0158->U+0159,U+0159,U+015A->U+015B,\
U+015B,U+015C->U+015D,U+015D,U+015E->U+015F,U+015F,U+0160->U+0161,\
U+0161,U+0162->U+0163,U+0163,U+0164->U+0165,U+0165,U+0166->U+0167,\
U+0167,U+0168->U+0169,U+0169,U+016A->U+016B,U+016B,U+016C->U+016D,\
U+016D,U+016E->U+016F,U+016F,U+0170->U+0171,U+0171,U+0172->U+0173,\
U+0173,U+0174->U+0175,U+0175,U+0176->U+0177,U+0177,U+0178->U+00FF,\
U+00FF,U+0179->U+017A,U+017A,U+017B->U+017C,U+017C,U+017D->U+017E,\
U+017E,U+0410..U+042F->U+0430..U+044F,U+0430..U+044F,U+05D0..U+05EA,\
U+0531..U+0556->U+0561..U+0586,U+0561..U+0587,U+0621..U+063A,U+01B9,\
U+01BF,U+0640..U+064A,U+0660..U+0669,U+066E,U+066F,U+0671..U+06D3,\
U+06F0..U+06FF,U+0904..U+0939,U+0958..U+095F,U+0960..U+0963,\
U+0966..U+096F,U+097B..U+097F,U+0985..U+09B9,U+09CE,U+09DC..U+09E3,\
U+09E6..U+09EF,U+0A05..U+0A39,U+0A59..U+0A5E,U+0A66..U+0A6F,\
U+0A85..U+0AB9,U+0AE0..U+0AE3,U+0AE6..U+0AEF,U+0B05..U+0B39,\
U+0B5C..U+0B61,U+0B66..U+0B6F,U+0B71,U+0B85..U+0BB9,U+0BE6..U+0BF2,\
U+0C05..U+0C39,U+0C66..U+0C6F,U+0C85..U+0CB9,U+0CDE..U+0CE3,\
U+0CE6..U+0CEF,U+0D05..U+0D39,U+0D60,U+0D61,U+0D66..U+0D6F,\
U+0D85..U+0DC6,U+1900..U+1938,U+1946..U+194F,U+A800..U+A805,\
U+A807..U+A822,U+0386->U+03B1,U+03AC->U+03B1,U+0388->U+03B5,\
U+03AD->U+03B5,U+0389->U+03B7,U+03AE->U+03B7,U+038A->U+03B9,\
U+0390->U+03B9,U+03AA->U+03B9,U+03AF->U+03B9,U+03CA->U+03B9,\
U+038C->U+03BF,U+03CC->U+03BF,U+038E->U+03C5,U+03AB->U+03C5,\
U+03B0->U+03C5,U+03CB->U+03C5,U+03CD->U+03C5,U+038F->U+03C9,\
U+03CE->U+03C9,U+03C2->U+03C3,U+0391..U+03A1->U+03B1..U+03C1,\
U+03A3..U+03A9->U+03C3..U+03C9,U+03B1..U+03C1,U+03C3..U+03C9,\
U+0E01..U+0E2E,U+0E30..U+0E3A,U+0E40..U+0E45,U+0E47,U+0E50..U+0E59,\
U+A000..U+A48F,U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
# 指明分詞法讀取詞典文件的位置,當啟用分詞法時,為必填項。
# 在使用 LibMMSeg 作為分詞庫時,需要確保詞典文件uni.lib在指定的目錄下。
# 再使用 LibMMSeg 分詞外掛時,這個設定值才有效,不然在建立索引時會出錯。
#charset_dictpath = dict
# 忽略的字符列表,預設為空。
#ignore_chars = U+AD
# 索引的最小前綴長度,預設為0(不索引前綴)。
#min_prefix_len = 3
# 索引的最小中綴長度,預設為0(不索引中綴)。
#min_infix_len = 3
# 做前綴索引的字段列表,預設為空(所有字段均為前綴索引模式)。
#prefix_fields = url, domain
# 做中綴索引的字段列表,預設為空(所有字段均為中綴索引模式)。
#infix_fields = url, domain
# 允許前綴/中綴索引上的星號語法(或稱萬用字符)預設為 0(不使用通配符),這是為了與0.9.7版本的兼容性。設定值有 0 和 1。
enable_star = 1
#分詞,設定值有 0,1,如果要搜索中文,請指定為 1
ngram_len = 1
# 分詞字符,中文搜索必要設定。
ngram_chars = U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
# 短語邊界字符列表,預設為空。
#phrase_boundary = ., ?, !, U+2026
# 是否從輸入全文數據中去除 HTML 標記。預設為 0。設定值有 0(禁用),1(啟用)。
html_strip = 0
# 去除 HTML 標籤時要索引標籤語言的屬性列表,預設為空(不索引標記語言屬性)。
# 指定被保留並索引的 HTML 標記語言屬性,即使其他 HTML 標記被刪除。
html_index_attrs = img=alt,title; a=title;
# HTML 標籤列表,不僅這些標籤本身會被刪除,標籤之間的文字內容也會被刪除。預設為空(不刪除任何元素的內容)。
html_remove_elements = style, script
}
index dist_tit
{
# 索引類型。預設為空(索引為簡單本地索引)。設定值有空字串或 "distributed"
type = distributed
# 分佈式索引(distributed index)中的本地索引聲明,可以設定多個,預設為空。
local = s_tit
# 分佈式索引(distributed index)中的遠程代理和索引聲明,可以設定多個,預設為空。
agent = localhost:3313:remote1
agent = localhost:3314:remote2,remote3
# 遠程代理的最大連接時間,單位為毫秒,預設為 1000(1 sec)。
agent_connect_timeout = 1000
# 遠程代理的最大查詢時間,單位為毫秒,預設為 3000(3 sec)。
agent_query_timeout = 3000
# 預先開啟全部索引文件還是每次查詢時再開啟索引。預設為0(不預先開啟)。
#preopen = 1
}
#------------------------------------------------------------------------
index s_txt
{
source = s_txt
path = /var/data/s_txt
docinfo = extern
mlock = 0
morphology = none
min_word_len = 1
charset_type = utf-8
charset_table = U+FF10..U+FF19->0..9,0..9,U+FF41..U+FF5A->a..z,\
U+FF21..U+FF3A->a..z,A..Z->a..z,a..z,U+0149,U+017F,U+0138,U+00DF,\
U+00FF,U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6,\
U+00D8..U+00DE->U+00F8..U+00FE,U+00F8..U+00FE,U+0100->U+0101,\
U+0101,U+0102->U+0103,U+0103,U+0104->U+0105,U+0105,U+0106->U+0107,\
U+0107,U+0108->U+0109,U+0109,U+010A->U+010B,U+010B,U+010C->U+010D,\
U+010D,U+010E->U+010F,U+010F,U+0110->U+0111,U+0111,U+0112->U+0113,\
U+0113,U+0114->U+0115,U+0115,U+0116->U+0117,U+0117,U+0118->U+0119,\
U+0119,U+011A->U+011B,U+011B,U+011C->U+011D,U+011D,U+011E->U+011F,\
U+011F,U+0130->U+0131,U+0131,U+0132->U+0133,U+0133,U+0134->U+0135,\
U+0135,U+0136->U+0137,U+0137,U+0139->U+013A,U+013A,U+013B->U+013C,\
U+013C,U+013D->U+013E,U+013E,U+013F->U+0140,U+0140,U+0141->U+0142,\
U+0142,U+0143->U+0144,U+0144,U+0145->U+0146,U+0146,U+0147->U+0148,\
U+0148,U+014A->U+014B,U+014B,U+014C->U+014D,U+014D,U+014E->U+014F,\
U+014F,U+0150->U+0151,U+0151,U+0152->U+0153,U+0153,U+0154->U+0155,\
U+0155,U+0156->U+0157,U+0157,U+0158->U+0159,U+0159,U+015A->U+015B,\
U+015B,U+015C->U+015D,U+015D,U+015E->U+015F,U+015F,U+0160->U+0161,\
U+0161,U+0162->U+0163,U+0163,U+0164->U+0165,U+0165,U+0166->U+0167,\
U+0167,U+0168->U+0169,U+0169,U+016A->U+016B,U+016B,U+016C->U+016D,\
U+016D,U+016E->U+016F,U+016F,U+0170->U+0171,U+0171,U+0172->U+0173,\
U+0173,U+0174->U+0175,U+0175,U+0176->U+0177,U+0177,U+0178->U+00FF,\
U+00FF,U+0179->U+017A,U+017A,U+017B->U+017C,U+017C,U+017D->U+017E,\
U+017E,U+0410..U+042F->U+0430..U+044F,U+0430..U+044F,U+05D0..U+05EA,\
U+0531..U+0556->U+0561..U+0586,U+0561..U+0587,U+0621..U+063A,U+01B9,\
U+01BF,U+0640..U+064A,U+0660..U+0669,U+066E,U+066F,U+0671..U+06D3,\
U+06F0..U+06FF,U+0904..U+0939,U+0958..U+095F,U+0960..U+0963,\
U+0966..U+096F,U+097B..U+097F,U+0985..U+09B9,U+09CE,U+09DC..U+09E3,\
U+09E6..U+09EF,U+0A05..U+0A39,U+0A59..U+0A5E,U+0A66..U+0A6F,\
U+0A85..U+0AB9,U+0AE0..U+0AE3,U+0AE6..U+0AEF,U+0B05..U+0B39,\
U+0B5C..U+0B61,U+0B66..U+0B6F,U+0B71,U+0B85..U+0BB9,U+0BE6..U+0BF2,\
U+0C05..U+0C39,U+0C66..U+0C6F,U+0C85..U+0CB9,U+0CDE..U+0CE3,\
U+0CE6..U+0CEF,U+0D05..U+0D39,U+0D60,U+0D61,U+0D66..U+0D6F,\
U+0D85..U+0DC6,U+1900..U+1938,U+1946..U+194F,U+A800..U+A805,\
U+A807..U+A822,U+0386->U+03B1,U+03AC->U+03B1,U+0388->U+03B5,\
U+03AD->U+03B5,U+0389->U+03B7,U+03AE->U+03B7,U+038A->U+03B9,\
U+0390->U+03B9,U+03AA->U+03B9,U+03AF->U+03B9,U+03CA->U+03B9,\
U+038C->U+03BF,U+03CC->U+03BF,U+038E->U+03C5,U+03AB->U+03C5,\
U+03B0->U+03C5,U+03CB->U+03C5,U+03CD->U+03C5,U+038F->U+03C9,\
U+03CE->U+03C9,U+03C2->U+03C3,U+0391..U+03A1->U+03B1..U+03C1,\
U+03A3..U+03A9->U+03C3..U+03C9,U+03B1..U+03C1,U+03C3..U+03C9,\
U+0E01..U+0E2E,U+0E30..U+0E3A,U+0E40..U+0E45,U+0E47,U+0E50..U+0E59,\
U+A000..U+A48F,U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
ngram_len = 1
ngram_chars = U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
html_strip = 0
html_index_attrs = img=alt,title; a=title;
html_remove_elements = style, script
}
index dist_txt
{
type = distributed
local = s_txt
agent = localhost:3313:remote1
agent = localhost:3314:remote2,remote3
agent_connect_timeout = 1000
agent_query_timeout = 3000
}
#------------------------------------------------------------------------
index s_comment
{
source = s_comment
path = /var/data/s_comment
docinfo = extern
mlock = 0
morphology = none
min_word_len = 1
charset_type = utf-8
charset_table = U+FF10..U+FF19->0..9,0..9,U+FF41..U+FF5A->a..z,\
U+FF21..U+FF3A->a..z,A..Z->a..z,a..z,U+0149,U+017F,U+0138,U+00DF,\
U+00FF,U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6,\
U+00D8..U+00DE->U+00F8..U+00FE,U+00F8..U+00FE,U+0100->U+0101,\
U+0101,U+0102->U+0103,U+0103,U+0104->U+0105,U+0105,U+0106->U+0107,\
U+0107,U+0108->U+0109,U+0109,U+010A->U+010B,U+010B,U+010C->U+010D,\
U+010D,U+010E->U+010F,U+010F,U+0110->U+0111,U+0111,U+0112->U+0113,\
U+0113,U+0114->U+0115,U+0115,U+0116->U+0117,U+0117,U+0118->U+0119,\
U+0119,U+011A->U+011B,U+011B,U+011C->U+011D,U+011D,U+011E->U+011F,\
U+011F,U+0130->U+0131,U+0131,U+0132->U+0133,U+0133,U+0134->U+0135,\
U+0135,U+0136->U+0137,U+0137,U+0139->U+013A,U+013A,U+013B->U+013C,\
U+013C,U+013D->U+013E,U+013E,U+013F->U+0140,U+0140,U+0141->U+0142,\
U+0142,U+0143->U+0144,U+0144,U+0145->U+0146,U+0146,U+0147->U+0148,\
U+0148,U+014A->U+014B,U+014B,U+014C->U+014D,U+014D,U+014E->U+014F,\
U+014F,U+0150->U+0151,U+0151,U+0152->U+0153,U+0153,U+0154->U+0155,\
U+0155,U+0156->U+0157,U+0157,U+0158->U+0159,U+0159,U+015A->U+015B,\
U+015B,U+015C->U+015D,U+015D,U+015E->U+015F,U+015F,U+0160->U+0161,\
U+0161,U+0162->U+0163,U+0163,U+0164->U+0165,U+0165,U+0166->U+0167,\
U+0167,U+0168->U+0169,U+0169,U+016A->U+016B,U+016B,U+016C->U+016D,\
U+016D,U+016E->U+016F,U+016F,U+0170->U+0171,U+0171,U+0172->U+0173,\
U+0173,U+0174->U+0175,U+0175,U+0176->U+0177,U+0177,U+0178->U+00FF,\
U+00FF,U+0179->U+017A,U+017A,U+017B->U+017C,U+017C,U+017D->U+017E,\
U+017E,U+0410..U+042F->U+0430..U+044F,U+0430..U+044F,U+05D0..U+05EA,\
U+0531..U+0556->U+0561..U+0586,U+0561..U+0587,U+0621..U+063A,U+01B9,\
U+01BF,U+0640..U+064A,U+0660..U+0669,U+066E,U+066F,U+0671..U+06D3,\
U+06F0..U+06FF,U+0904..U+0939,U+0958..U+095F,U+0960..U+0963,\
U+0966..U+096F,U+097B..U+097F,U+0985..U+09B9,U+09CE,U+09DC..U+09E3,\
U+09E6..U+09EF,U+0A05..U+0A39,U+0A59..U+0A5E,U+0A66..U+0A6F,\
U+0A85..U+0AB9,U+0AE0..U+0AE3,U+0AE6..U+0AEF,U+0B05..U+0B39,\
U+0B5C..U+0B61,U+0B66..U+0B6F,U+0B71,U+0B85..U+0BB9,U+0BE6..U+0BF2,\
U+0C05..U+0C39,U+0C66..U+0C6F,U+0C85..U+0CB9,U+0CDE..U+0CE3,\
U+0CE6..U+0CEF,U+0D05..U+0D39,U+0D60,U+0D61,U+0D66..U+0D6F,\
U+0D85..U+0DC6,U+1900..U+1938,U+1946..U+194F,U+A800..U+A805,\
U+A807..U+A822,U+0386->U+03B1,U+03AC->U+03B1,U+0388->U+03B5,\
U+03AD->U+03B5,U+0389->U+03B7,U+03AE->U+03B7,U+038A->U+03B9,\
U+0390->U+03B9,U+03AA->U+03B9,U+03AF->U+03B9,U+03CA->U+03B9,\
U+038C->U+03BF,U+03CC->U+03BF,U+038E->U+03C5,U+03AB->U+03C5,\
U+03B0->U+03C5,U+03CB->U+03C5,U+03CD->U+03C5,U+038F->U+03C9,\
U+03CE->U+03C9,U+03C2->U+03C3,U+0391..U+03A1->U+03B1..U+03C1,\
U+03A3..U+03A9->U+03C3..U+03C9,U+03B1..U+03C1,U+03C3..U+03C9,\
U+0E01..U+0E2E,U+0E30..U+0E3A,U+0E40..U+0E45,U+0E47,U+0E50..U+0E59,\
U+A000..U+A48F,U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
ngram_len = 1
ngram_chars = U+4E00..U+9FBF,U+3400..U+4DBF,U+20000..U+2A6DF,\
U+F900..U+FAFF,U+2F800..U+2FA1F,U+2E80..U+2EFF,U+2F00..U+2FDF,\
U+3100..U+312F,U+31A0..U+31BF,U+3040..U+309F,U+30A0..U+30FF,\
U+31F0..U+31FF,U+AC00..U+D7AF,U+1100..U+11FF,U+3130..U+318F,\
U+A000..U+A48F,U+A490..U+A4CF
html_strip = 0
html_index_attrs = img=alt,title; a=title;
html_remove_elements = style, script
}
index dist_comment
{
type = distributed
local = s_comment
agent = localhost:3313:remote1
agent = localhost:3314:remote2,remote3
agent_connect_timeout = 1000
agent_query_timeout = 3000
}
###############################################################
### indexer settings
###############################################################
indexer
{
# 索引過程中記憶體的使用限制,預設為 32M。
mem_limit = 64M
# 每秒最大 I/O 操作次數,用於限制 I/O 操作。預設為0(無限制)。
#max_iops = 40
# 最大單次允許的 I/O 操作大小,以 bytes 為單位,用於I/O節流。預設為0(不限制)。
#max_iosize = 1048576
}
###############################################################
### searchd settings
###############################################################
searchd
{
# 監聽來源 IP,預設為0.0.0.0(即允許所有 IP 連結)。
#address = 127.0.0.1
# searchd 的 TCP port。預設為 3312。
port = 3312
# log 的紀錄文件位址,全部 searchd 運行時事件會被記錄在這個日誌文件中。
log = /var/log/sphinx/searchd.log
# 查詢日誌文件名,預設為空(不記錄查詢日誌)。
# 全部搜索查詢會被記錄在此文件中。
query_log = /var/log/sphinx/query.log
# 最大的查詢請求時間,單位是秒。預設是5秒。
# searchd 將強制關閉在此時間內未能成功發出查詢的客戶端連接。
read_timeout = 5
# 並行執行的搜索的數目。預設為0(無限制)。
max_children = 30
# searchd 進程 ID 文件名。必選項。
pid_file = /var/log/sphinx/searchd.pid
# 守護進程在記憶體中為每個索引所保持並返回給客戶端的匹配數目的最大值。預設為1000
max_matches = 1000
# 防止 searchd 輪換在需要預取大量數據的索引時停止響應。預設為1(啟用無縫(seamless)輪換)
seamless_rotate = 1
# 是否在啟動時強制重新打開所有索引文件。預設為0(不重新打開)。
preopen_indexes = 0
# 索引輪換成功之後,是否刪除以.old為擴展名的索引拷貝。預設為1(刪除這些索引拷貝)。
unlink_old = 1
}
# --eof--
</pre><br />
<br />
<br />
<h4><a name="sphinx_start"></a>啟動搜尋引擎</h4>開始建立索引及啟動搜尋引擎<br />
<pre class="sh" name="code"># 為所有 sphinx.conf 中設定的資料來源建立索引
/usr/local/bin/indexer --config /usr/local/etc/sphinx.conf --all
# 以 sphinx.conf 中的設定啟動搜尋引擎
/usr/local/bin/searchd --config /usr/local/etc/sphinx.conf
</pre><br />
<br />
動態更新索引的方法<br />
<pre class="sh" name="code"># 更新所有資料來源
/usr/local/bin/indexer --rotate --config /usr/local/etc/sphinx.conf --all
# 更新特定的資料來源(s_tit),並且不顯示任何訊息
/usr/local/bin/indexer --quiet --rotate --config /usr/local/etc/sphinx.conf s_tit
</pre><br />
<br />
<br />
<h4><a name="sphinx_software"></a>主要程式及運作方式</h4>Sphinx 主要有以下部分:<br />
<ul><li><strong>indexer</strong>: 建立索引庫的程序,在查詢前必須先建立索引庫。<br />
</li>
<li><strong>search</strong>: 提供 console 下的搜尋介面,可用於測試用。<br />
</li>
<li><strong>searchd</strong>: 主要的 service 程序,以 port 為連接介面去對索引庫取得資料。<br />
</li>
<li><strong>SphinxAPI</strong>: 連結 searchd 的客戶端 API,目前支援的 script 語言有(PHP, Python, Perl, Ruby)。<br />
</li>
<li><strong>SphinxSE</strong>: 透過 MySQL 連結 searchd 的資料表引擎。</li>
</ul><br />
<a href="http://3.bp.blogspot.com/_b8lN_UbLoEc/SfxX6fhPoOI/AAAAAAAAEP8/6vcfnSO72tQ/Sphinx%E6%9E%B6%E6%A7%8B%E5%9C%96.jpg"><img src="http://3.bp.blogspot.com/_b8lN_UbLoEc/SfxX6fhPoOI/AAAAAAAAEP8/6vcfnSO72tQ/s512/Sphinx%E6%9E%B6%E6%A7%8B%E5%9C%96.jpg" alt="Sphinx 架構圖"id="BLOGGER_PHOTO_ID_5331232721304789218" /></a><br />
<br />
<br />
<h4><a name="sphinx_command"></a>命令列參數說明</h4><strong>indexer</strong><br />
格式:indexer [OPTIONS] [indexname1 [indexname2 [...]]]<br />
<ul><li><strong>--config <file></strong> (<strong>-c <file></strong>精簡指令) 指定 sphinx.conf 的位址,預設為 /usr/local/etc/sphinx.conf<br />
</li>
<li><strong>--all</strong> 為所有資料來源建立索引,在利用 cron 定期更新索引庫時,可使用此參數更新所有資料來源的索引<br />
</li>
<li><strong>--rotate</strong> 動態更新來源索引,可用在 searchd 仍處於啟動的狀態。<br />
</li>
<li><strong>--quiet</strong> 告訴 indexer 不輸出任何訊息,除非有一個錯誤。在 cron 上使用時非常方便。<br />
</li>
<li><strong>--noprogress</strong> 不顯示進展的細節訊息。而在最後輸出的細節訊息(如文件索引,索引的速度等等)<br />
</li>
<li><strong>--merge <dst-index> <src-index></strong> 合併 <src-index> 到 <dst-index>,<dst-index>會保存有合併後的結果,<src-index> 不會被修改。<br />
</li>
<li><strong>--merge-dst-range <attr> <min> <max></strong>合併時過濾 <dst-index> 僅保留 <attr> 值在 <min> 和 <max> (包含)的記錄.</li>
</ul><br />
<br />
<strong>searchd</strong><br />
格式:searchd [OPTIONS]<br />
<ul><li><strong>--help</strong> (<strong>-h</strong> 精簡指令) 列出所有的參數說明。<br />
</li>
<li><strong>--config <file></strong> (<strong>-c <file></strong> 精簡指令) 指定 sphinx.conf 的位址,預設為 /usr/local/etc/sphinx.conf<br />
</li>
<li><strong>--stop</strong> 停止 searchd 引擎的運作<br />
</li>
<li><strong>--console</strong> 強制使用 console 介面(windows)<br />
</li>
<li><strong>--port portnumber</strong> (<strong>-p</strong> 精簡指令) 強制變更 searchd 連結端口(port)的位址<br />
</li>
<li><strong>--index <index></strong> (<strong>-i</strong> 精簡指令) 指定唯一的搜尋索引,主要用於測試用。</li>
</ul><br />
<br />
<strong>search</strong><br />
格式:search [OPTIONS] word1 [word2 [word3 [...]]]<br />
<br />
一般選項:<br />
<ul><li><strong>--config <file></strong>(<strong>-c <file></strong> 精簡指令) 讓 search 使用特定的 conf 檔作為配置,就像前面 indexer。<br />
</li>
<li><strong>--index <index></strong>(<strong>-i <index></strong> 精簡指令) 讓 search 限制搜尋的索引檔,預設會嘗試搜尋所有 conf 檔中列出的索引。<br />
</li>
<li><strong>--stdin</strong> tells <strong class="filename">search</strong> 讓 search 接受查詢從標準輸入,而不是命令行。這可用在測試目的上,即可以透過管線輸入或 script。</li>
</ul><br />
<br />
設置匹配選項:<br />
<ul><li><strong>--any</strong> (<strong>-a</strong> 精簡指令) 匹配所有的搜尋關鍵字。<br />
</li>
<li><strong>--phrase</strong> (<strong>-p</strong> 精簡指令) 短語匹配。<br />
</li>
<li><strong>--boolean</strong> (<strong>-b</strong> 精簡指令) 布林表達式匹配。<br />
</li>
<li><strong>--ext</strong> (<strong>-e</strong> 精簡指令) 查詢匹配一個Sphinx內部查詢語言表達式。<br />
</li>
<li><strong>--ext2</strong> (<strong>-e2</strong> 精簡指令) 查詢匹配一個Sphinx內部查詢語言表達式。<br />
</li>
<li><strong>--filter <attr> <v></strong> (<strong>-f <attr> <v></strong> 精簡指令) 過慮條件,可用在過慮 conf 中以 attr 標註的欄位,(--filter InsertDate 2009)過慮出建立日期為 2009 的資料</li>
</ul><br />
<br />
處理結果輸出選項:<br />
<ul><li><strong>--limit <count></strong>(<strong>-l count</strong> 精簡指令) 輸出 row 的數量(預設為20)。<br />
</li>
<li><strong>--offset <count></strong>(<strong>-o <count></strong>精簡指令) 輸出 row 的起始(預設為0)。<br />
</li>
<li><strong>--group <attr></strong>(<strong>-g <attr></strong>精簡指令) specifies that results should be grouped together based on the attribute specified. Like the GROUP BY clause in SQL, it will combine all results where the attribute given matches, and returns a set of results where each returned result is the best from each group. Unless otherwise specified, this will be the best match on relevance.<br />
</li>
<li><strong>--groupsort <expr></strong>(<strong>-gs <expr></strong>精簡指令) instructs that when results are grouped with <code>-group</code>, the expression given in <expr>shall determine the order of the groups. Note, this does not specify which is the best item within the group, only the order in which the groups themselves shall be returned.<br />
</li>
<li><strong>--sortby <clause></strong>(<strong>-s <clause></strong>精簡指令) specifies that results should be sorted in the order listed in <clause>. This allows you to specify the order you wish results to be presented in, ordering by different columns. For example, you could say <code>--sortby "@weight DESC entrytime DESC"</code> to sort entries first by weight (or relevance) and where two or more entries have the same weight, to then sort by the time with the highest time (newest) first. You will usually need to put the items in quotes (<code>--sortby "@weight DESC"</code>) or use commas (<code>--sortby @weight,DESC</code>) to avoid the items being treated separately. Additionally, like the regular sorting modes, if <code>--group</code> (grouping) is being used, this will state how to establish the best match within each group.<br />
</li>
<li><strong>--sortexpr expr</strong> (<strong>-S expr</strong> 精簡指令) specifies that the search results should be presented in an order determined by an arithmetic expression, stated in expr. For example: <code>--sortexpr "@weight + ( user_karma + ln(pageviews) )*0.1"</code> (again noting that this will have to be quoted to avoid the shell dealing with the asterisk). Extended sort mode is discussed in more detail under the <code>SPH_SORT_EXTENDED</code> entry under the Sorting modes chapter of the manual.<br />
</li>
<li><strong>--sort=date</strong> 指定以降幕排序的欄位,必須在 conf 中以 attr 標註的欄位。<br />
</li>
<li><strong>--rsort=date</strong> 指定以升幕排序的欄位,必須在 conf 中以 attr 標註的欄位。</li>
</ul><br />
<br />
<br />
<h4><a name="sphinx_abstract"></a>摘要使用</h4>Sphinx 的 API 提供摘要產生的功能<br />
在下載的安裝包中可以找到這份範例<br />
<pre class="php" name="code"><?php
//
// $Id: test2.php 910 2007-11-16 11:43:46Z shodan $
//
require ( "sphinxapi.php" );
$docs = array
(
"this is my test text to be highlighted, and for the sake ".
"of the testing we need to pump its length somewhat",
"another test text to be highlighted, below limit",
"test number three, without phrase match",
"final test, not only without phrase match, but also above ".
"limit and with swapped phrase text test as well",
);
$words = "test text";
$index = "test1";
$opts = array
(
"before_match" => "<b>",
"after_match" => "</b>",
"chunk_separator" => " ... ",
"limit" => 60,
"around" => 3,
);
foreach ( array(0,1) as $exact )
{
$opts["exact_phrase"] = $exact;
print "exact_phrase=$exact\n";
$cl = new SphinxClient ();
$res = $cl->BuildExcerpts ( $docs, $index, $words, $opts );
if ( !$res )
{
die ( "ERROR: " . $cl->GetLastError() . ".\n" );
} else
{
$n = 0;
foreach ( $res as $entry )
{
$n++;
print "n=$n, res=$entry\n";
}
print "\n";
}
}
//
// $Id: test2.php 910 2007-11-16 11:43:46Z shodan $
//
?>
</pre><br />
<br />
<br />
<h4><a name="sphinxse_matching"></a>搜尋的匹配模式</h4><table border="1" style="font-size:11px; width=98%;"><tr><th width="80">匹配模式</th><th>SPH_MATCH_ALL<br />
匹配所有</th><th>SPH_MATCH_ANY<br />
匹配任意</th><th>SPH_MATCH_PHRASE<br />
短语匹配</th><th>SPH_MATCH_EXTENDED<br />
内部查询</th></tr>
<tr><th>觀察</th><td>必須包含前後順序,所有 word 都要包含。</td><td>word 符合前後順序權重高,中文會拆字查詢。</td><td>必須包前後順序,而且是有序比對(排除標點符號)。</td><td>無特定前後順序,但有符合前後順序的權重比較高。</td></tr>
<tr><th>權重特性</th><td>Max match_word lemgth</td><td>(match_word1 lemgth)^2+<br />
(match_word2 lemgth)^2</td><td>match_word lemgth</td><td>BM25</td></tr>
<tr><th>最高權重值</th><td>{順序完全批配的word數}</td><td>{所有 word 數}^2</td><td>{word數}<br />
沒有較小的權重,此為唯一權重值。</td><td>?</td></tr>
<tr><th>word 對結果的影響</th><td>用所有的 word 進行查詢,只要有一個 word 不符合就不撈出來。</td><td>符合其中一個word。</td><td>完全符合前後順序與所有word。</td><td>符合全數word。</td></tr>
<tr><th>查詢時 word 的先後</th><td>可以不同。</td><td>正確的順序權重較高。</td><td>必要。順序不同不會出現在結果裡。</td><td>前後順序影響權重。</td></tr>
<tr><th>適合用途</th><td>適合不會打錯的查詢,word 很少的查詢。</td><td>多 word 的查詢。</td><td>完全精準查詢。</td><td>適合不會打錯的查詢,word 很少的查詢。</td></tr>
<tr><th>特性</th><td>word 越多,查詢越少。有一個 word 沒有就找不到了。</td><td>word 越多,資料越多。</td><td>幾乎等於 SQL like。</td><td>word 越多,資料越少,權重值會拉開,且權重值是根據所有可能結果之間的差異做權重。</td></tr>
</table><br />
<br />
<br />
<h4><a name="sphinxse_table"></a>建立 SphinxSE 表</h4>SphinxSE 的使用方式是利用一個虛擬的資料表去與 searchd 作連結<br />
這個表本身不會儲存任何資料,也不能新增資料<br />
利用這個資料表可以作任何 SQL SELECT 的操作(JOIN ...)<br />
<pre class="sql" name="code">-- @ sphinx_interface(Sphinx搜尋連接介面)
CREATE TABLE `sphinx_interface` (
-- 前三個為必要欄位,
-- 欄位屬性順序必須為 INTEGER,INTEGER,VARCHAR
-- 分別標記為(id),匹配權重(weight),查詢(query)
-- 不限定欄位名稱
-- 同時 id 及 query 必須建立索引。
`id` INT NOT NULL COMMENT '搜尋結果的 Id',
`weight` INT NOT NULL COMMENT '搜尋結果的權重',
`query` VARCHAR(3072) NOT NULL COMMENT '搜尋的查詢條件',
-- 額外欄位,需與 sphinx.conf 中 sql_attr 設定的欄位一致,
-- 欄位屬性必須為 INTEGER,VARCHAR 或 TIMESTAMP,
-- 此處的設定可作後續的排序或過濾用。
`insertdate` VARCHAR(3072) NOT NULL COMMENT '日期',
`commentnum` INTEGER COMMENT '回應總數',
INDEX(id),
INDEX(query)
)ENGINE=SPHINX
-- CONNECTION 的格式為 sphinx://HOST:PORT/INDEXNAME
-- 建議先不加 INDEXNAME,等在查詢時在決定 index
CONNECTION="sphinx://localhost:3312/"
COMMENT='Sphinx搜尋連接介面';
</pre><br />
<br />
<br />
<h4><a name="sphinxse_use"></a>SphinxSE 的使用方式</h4><ul><li><strong>query</strong> 查詢文本<br />
<br />
</li>
<li><strong>mode</strong> 匹配模式.必須是 "all", "any", "phrase", "boolean" 或 "extended",預設為“all”<br />
<br />
</li>
<li><strong>sort</strong> 匹配項排序模式必須是“relevance”, “attr_desc”, “attr_asc”, “time_segments”或“extended”之一。除了“relevance”模式,其他模式中還必須在一個冒號後附上屬性名(或“extended”模式中的排序子句)。<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;sort=attr_asc:group_id';
... WHERE query='test;sort=extended:@weight desc, group_id asc';</pre><br />
</li>
<li><strong>offset</strong> 結果集中的偏移量,預設是0。<br />
<br />
</li>
<li><strong>limit</strong> 從結果集中獲取的匹配項數目,預設為20。<br />
<br />
</li>
<li><strong>index</strong> 待搜索的索引:<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;index=test1;';
... WHERE query='test;index=test1,test2,test3;';</pre><br />
</li>
<li><strong>minid</strong> , <strong>maxid</strong> 匹配文檔ID的最小值和最大值<br />
<br />
</li>
<li><strong>weights</strong> 逗號分隔的列表,指定Sphinx全文數據字段的權值<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;weights=1,2,3;';</pre><br />
</li>
<li><strong>filter</strong> , <strong>!filter</strong> 逗號分隔的列表,指定一個屬性名和一系列可匹配的屬性值:<br />
<pre class="sql:nocontrols" name="code">-- 僅包括群組 1, 5 和 19
... WHERE query='test;filter=group_id,1,5,19;';
-- 排除的群組 3 和 11
... WHERE query='test;!filter=group_id,3,11;';</pre><br />
</li>
<li><strong>range</strong> , <strong>!range</strong> 逗號分隔的列表,指定一個屬性名和該屬性可匹配的最小值和最大值:<br />
<pre class="sql:nocontrols" name="code">-- 僅包括群組 3 至 7 之間 的 group_id
... WHERE query='test;range=group_id,3,7;';
-- 排除的群組 5 至 25 之間的 group_id
... WHERE query='test;!range=group_id,5,25;';</pre><br />
</li>
<li><strong>maxmatches</strong> 此查詢最大匹配的數量:<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;maxmatches=2000;';</pre><br />
</li>
<li><strong>groupby</strong> 分組(group-by)函數和屬性:<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;groupby=day:published_ts;';
... WHERE query='test;groupby=attr:group_id;';</pre><br />
</li>
<li><strong>groupsort</strong> 分組(group-by)排序子句<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;groupsort=@count desc;';</pre><br />
</li>
<li><strong>indexweights</strong> 逗號分隔的列表,指定一系列索引名和搜索時這些索引對應的權值<br />
<pre class="sql:nocontrols" name="code">... WHERE query='test;indexweights=idx_exact,2,idx_stemmed,1;';</pre></li>
</ul><br />
<h4><a name="sphinxse_reference"></a>參考來源</h4><a href="http://www.sphinxsearch.com/docs/manual-0.9.8.html" target="_blank">Sphinx 0.9.8.1 reference manual</a> <br />
<a href="http://www.coreseek.cn/uploads/pdf/sphinx_doc_zhcn_0.9.pdf" target="_blank">Coreseek 全文檢索服務器2.0 (Sphinx 0.9.8) 參考手冊</a> <br />
<a href="http://dev.cgfinal.com/sphinx/sphinx.html" target="_blank">Sphinx速成指南</a><br />
<br />
<a href="http://blog.jctalk.info/2008/11/sphinx.html" target="_blank">Sphinx 自由開放原始碼全文搜尋引擎</a> <br />
<a href="http://www.xxlinux.com/linux/article/development/database/20090108/14811_3.html" target="_blank">ubuntu下Mysql+sphinx+中文分词安装配置</a> <br />
<a href="http://www.chineselinuxuniversity.net/articles/18720.shtml" target="_blank">Mysql+sphinx+中文分词简介(ubuntu)</a> <br />
<a href="http://www.ibm.com/developerworks/cn/opensource/os-php-sphinxsearch/index.html" target="_blank">用 PHP 构建自定义搜索引擎</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com7tag:blogger.com,1999:blog-5946530704742130970.post-89496336330113784702009-03-31T22:43:00.008+08:002013-06-11T21:40:51.040+08:00REPLACE INTO 和 INSERT INTO 的區别[MySQL]REPLACE 與INSERT 在 Syntax 及功能上都很類似<br />
在插入資料遇到 PRIMARY KEY 或 UNIQUE KEY 碰撞時<br />
REPLACE 會將<span style="color: rgb(255, 0, 0); font-weight: bold;">原本的資料刪除</span>再執行新增的動作<br />
<br />
這裡特別要注意的是會將<span style="color: rgb(255, 0, 0); font-weight: bold;">資料刪除</span><br />
並不只是使用複寫方式去處理的<br />
<br />
利用 REPLACE for SELECT 來處理資料真的很方便<br />
不必擔心資料碰撞的問題<br />
但請記得 REPLACE 的特性<br />
有時候事情不是想像中那麼快樂的<br />
<br />
REPLACE Syntax:<br />
<pre class="sql" name="code">REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
-- 或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
-- 或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
</pre><br />
<br />
參考來源:<br />
<a target="_blank" href="http://www.ccvita.com/206.html">MySQL Replace INTO的使用</a><br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.0/en/replace.html">MySQL 5.0 REPLACE Syntax</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-81768440938294197532009-03-31T21:24:00.007+08:002013-06-11T21:41:50.909+08:00將查詢結果更新至資料表 UPDATE for SELECT [MySQL]今天又學到一個 MySQL 新方法,原本就在找利用 SELECT 的結果 UPDATE 至資料表的方法,最後終於在 MySQL 官網中的回應裡找到了<br />
<pre class="sql" name="code">UPDATE
`t1` AS A,
(SELECT `b1`, COUNT(*) AS `total` FROM `t2` GROUP BY `c1`) AS B
SET A.`a2`=B.`total`
WHERE A.`a1`=B.`a1`
</pre><br />
<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.0/en/update.html">MySQL5.0: UPDATE Syntax</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-71644300220487727922008-06-07T21:14:00.008+08:002012-02-12T23:32:57.054+08:00將查詢結果新增至資料表 INSERT for SELECT [MySQL]最近愛上的這個技巧,一整個就只有方便而已,將查詢結果直接寫入資料表中,唯一的缺點就是沒有錯誤跳過,<span style="color: rgb(255, 0, 0); font-weight: bold;">只要錯了一行,後面的筆數就會被放棄</span>,所以在 SELECT 的時候就要<span style="color: rgb(255, 0, 0); font-weight: bold;">濾掉會錯誤的筆數</span>。<br /><pre class="sql" name="code"><br />INSERT INTO orderlist (book_id, last_time)<br /> SELECT book_id, MAX(time) FROM orders;<br /></pre><br /><br />參考文章:<br /><a href="http://function1122.blogspot.com/2008/01/mysql.html">MySQL 將查詢結果存入資料表</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-20841793259425893952008-06-06T23:00:00.008+08:002013-06-11T21:04:08.262+08:00GROUP_CONCAT 筆數串接函數 [MySQL]這是一個跟 COUNT() 類似的聚總函數,用來串接結果,函數說明如下:<br />
<pre class="sql" name="code">GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
-- DISTINCT 唯一值設定
-- ORDER BY 排序設定
-- ASC|DESC 排序方式
-- SEPARATOR 間隔字串(預設是 ',')
</pre><br />
<br />
不過今天在使用時卻發現忽視已久的問題,這個函數是有上限值的,可以透過設定 group_concat_max_len 變數變更(預設 1024)<br />
<br />
參考文章:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">MySQL 5.0 Reference Manual :: GROUP BY (Aggregate) Functions</a><br />
<a target="_blank" href="http://hi.baidu.com/tonny_dxf/blog/item/6d5a5c4c28b2cbfcd62afc01.html">MySQL的几个实用字符串函数</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-50788920209812191362008-06-04T22:30:00.008+08:002013-06-11T21:06:04.653+08:00CASE 指令,MySQL 的 switch 與 if else今天在玩 CASE 這個指令,雖然已經在同學 <a target="_blank" href="http://www.wretch.cc/blog/chunminy">黑色 深淵</a> 的 Blog 中看到過了,為了明確瞭解這個指令的語法及功能,今天花了一點時間實證。<br />
<ol><li style="color: rgb(255, 0, 0); font-weight: bold;">必須依附在 SELECT,UPDATE,INSERT,DELETE 下</li>
<li style="color: rgb(255, 0, 0); font-weight: bold;">不可用在區段中加其他動作</li>
<li style="color: rgb(255, 0, 0); font-weight: bold;">具有 switch 與 if else 兩種架構</li>
</ol><br />
<pre class="sql" name="code">-- switch 的用法
SELECT CASE col
WHEN 100 THEN '1'
WHEN 50 THEN '2'
ELSE '3'
END
FROM table;
-- if else 的用法
SELECT CASE
WHEN col>100 THEN '1'
WHEN col>50 THEN '2'
ELSE '3'
END
FROM table;
</pre><br />
<br />
在需要對欄位做多種情況區分時,這個指令就很好用,可惜<span style="color: rgb(255, 0, 0); font-weight: bold;">不行加入其他動作</span>,例如:<br />
<pre class="sql" name="code">CASE
WHEN EXISTS(SELECT * FROM table WHERE id=1)
THEN (UPDATE table SET txt='test' WHERE id=1)
ELSE (INSERT INTO table(id,txt) VALUES(1,'text'))
END;
</pre><br />
<br />
參考文件:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.1/en/case-statement.html">MySQL 5.1 Reference Manual :: 21.2.10.2 CASE Statement</a><br />
<a target="_blank" href="http://www.knowsky.com/345280.html">mysql语句中用if的例子</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com4tag:blogger.com,1999:blog-5946530704742130970.post-5434713641998153892008-06-03T23:34:00.010+08:002012-02-12T23:32:57.058+08:00IF(exp1,exp2,exp3) 好用的 MySQL 函數最近在找 MySQL 函數時,忽然看到這個函數,它的使用方法就跟<span style="color: rgb(255, 0, 0); font-weight: bold;">三元運算子</span>一樣,在 select 時做資料判別還蠻方便的,而且查詢時的效率也沒有太大的影響。<br /><pre class="sql" name="code"><br />IF(exp1,exp2,exp3)<br />-- 當 exp1 為 true 時就丟出 exp2<br />-- 為 false 則丟出 exp3<br /><br />-- 這個函數是可以允許疊加<br />IF(exp1,<br /> IF(exp21,exp22,exp23),<br /> IF(exp31,exp32,exp33)<br />)<br /><br />-- EX:<br />SELECT IF(a='1','yes','no');<br /></pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0