mysql常用日期query

1. 當天的資料
   

   select * from table where to_days(column_time) = to_days(now());
   select * from table where date(column_time) = curdate(); 

2. Between日期
   

   WHERE (created BETWEEN '2012-06-19' and '2012-06-30 23:59:59' ); 

   注意結尾是最後一天一秒，否則只會出現到前一天的資料
3. 最近7天,1個月
   

   WHERE DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) <= `created_date`;
   WHERE DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= `created_date`;

4. 本周
   從當週的Sunday開始到星期六的七天資料
   

   select * from wap_content where week(created_at) = week(now);
   

Reference

• sql的between與查詢日期範圍
  
• MYSQL查询一周内的数据(最近7天的)怎么写

MySQL Proxy & Amoeba

做MySQL的效能多會用load balance
目前MySQL的load balance多採用官方的MySQL Proxy及大陸的Amoeba
以下整理網路上收集到的資訊，挺多簡體翻的 要習慣一下

• MySQL Proxy
  安裝：mysql proxy讀寫分流(一)-mysql proxy的安裝方式
  說明：MySQL Proxy位於 client及MySQL server(s)中間
  
  MySQL Proxy可以monitor, analyze or transform their communication. 可達到:
  
  • load balancing
  • failover
  • query analysis
  • query filtering and modification
  • ... and many more. 
• Amoeba
  安裝：Amoeba For MySQL 實現資料庫負載平衡(讀寫分離)
  說明：Amoeba 也位於Client、Database Server(s)之間
  
   Amoeba 具有負載均衡、高可用性、 sql過濾
  可承受達到讀寫分離、Query Route（解析sql query語句，並且根據條件與預先設定的規則，請求到指定的目標數據庫。可並發請求多台數據庫合併結果）
  對客戶端透明。主要降低數據切分帶來的複雜多數據庫結構、數據切分規則給應用
  帶來的影響。

比較 in action

• Load Blance及讀寫分離
  Master：Server1（可讀寫）
  Slaves：Server2, Server3, Server4（3個平等的數據庫。只讀/負載均衡）
  
  
  • MySQL Proxy
    #設定db server變數
Server1=10.1.1.1  #master
Server2=10.2.1.1  #slave1
Server3=10.3.1.1  #slave2
Server4=10.4.1.1  #slave3
ROOT_DIR=/usr/local

#指定讀寫server
LUA_PATH="$ROOT_DIR/mysql-proxy/share/mysql-proxy/?.lua" $ROOT_DIR/mysql-proxy/sbin/mysql-proxy \
--daemon \
--proxy-backend-addresses=$Server1:3306 \
--proxy-read-only-backend-addresses=$Server2:3306 \
--proxy-read-only-backend-addresses=$Server3:3306 \
--proxy-read-only-backend-addresses=$Server4:3306 \
--proxy-lua-script=$ROOT_DIR/mysql-proxy/share/mysql-proxy/rw-splitting.lua
    這個例子中10.1.1.1為可寫
    限制10.2.1.1,10.3.1.1,10.4.1.1只能讀
    
  • Amoeba
    amoeba提供讀寫分離pool相關配置。並且提供負載均衡配置。
    可配置server2、server3、server4形成一個虛擬的 virtualSlave,該配置提供負載均衡、failOver、故障恢復功能
    <dbServer name=“virtualSlave” virtual=“true”>
<poolConfig>
<className>com.meidusa.amoeba.server.MultipleServerPool</className>
<!-- 負載均衡參數 1=ROUNDROBIN , 2=WEIGHTBASED -->
<property name="loadbalance">1</property>

<!-- 參與該pool負載均衡的poolName列表以逗號分割 -->
<property name="poolNames"> Server2,Server3,Server4</property>
</poolConfig>
</dbServer>
    指派讀寫機
    <queryRouter>
<className>com.meidusa.amoeba.mysql.parser.MysqlQueryRouter</className>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property>

<property name="writePool">server1</property>
<property name="readPool">virtualSlave</property>

<property name="needParse">true</property>
</queryRouter>
那麼遇到update/insert/delete將 query語句發送到 wirtePool，將 select發送到 readPool機器中執行。
• 資料分割
  
  • MySQL Proxy
    
  • Amoeba
    SELECT * from user_event where user_id='test' and gmt_create between Sysdate() -1 and Sysdate()
    
    如果根據gmt_create 時間進行數據切分，比如 6個月進行切分一次
    amoeba提供利用類似sql表達式進行數據切分：
    
    • 規則1(對應服務器1)：
      GMT_CREATE > to_date('2008-01-01','yyyy-mm-dd')
AND GMT_CREATE < to_date('2008-05-31','yyyy-mm-dd')
    • 規則2(對應服務器2)
      GMT_CREATE > to_date('2008-06-01','yyyy-mm-dd')
AND GMT_CREATE < to_date('2008-12-31','yyyy-mm-dd')
    上面的sql的條件 gmt_create 與規則裡面的的gmt_create 進行交集判斷，如果存在交集則表示符合規則。 則會將sql轉移到 規則1 的相應的服務器上面執行。
    利用amoeba寫出這種類似規則很容易，但是要想做到數據切分以後可線性擴容，那麼這樣的規則需要自己根據業務實際情況進行設置。
    amoeba可同時將sql 並發分發到多台服務器、然後將結果合併再反饋給客戶端，而且amoeba內部現成採用無阻塞模式，工作線程是不會等待的,並發請求多台 database server情況下，客戶端等待的時間基本上面是性能最差的那台 database server＋amoeba內部解析協議的時間

注意事項

沒實作過，也不知要注意什麼 還是看別人寫的吧
• MySQL Proxy
  
  • 同步延遲問題
    寫入後，其他slaves的同步問題，MySQL Proxy如何解決同步延遲問題
  • 資料是亂碼
    有看到2種解決方法，還沒試過
    
    • 設定mysql server
      set names "utf8"也沒有效果。
      [mysqld]
      skip-character-set-client-handshake
init-connect='SET NAMES utf8'
default-character-set=utf8
    • 修改/etc/my.cnf 設定檔
      加入底下設定，即可解決亂碼問題(當然您要依據您的環境做修改)
      default-character-set = utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8
• Amoeba
    MySQL使用Amoeba作为Proxy时的注意事项 以下是截錄的
  
  • Amoeba不支持事务
    p.s. 還不知道"事務"是什麼 不過官網似乎提到有支援了
  • Amoeba不支持跨库join和排序
    跨庫的join和排序非常消耗資源，會導致性能嚴重下降，Amoeba沒有進行支持，只能做到分數據庫實例。
  • Amoeba不支持大數據量的查詢。
    不適合返回大量(超過10萬)數據的查詢，大數據量的查詢非常消耗內存，Amoeba在進行大數據量查詢時性能會非常差。當然，實際業務中需要進行大數據量查詢的情況會非常少或者根本沒必要實現這種情況。這裡所謂的大數據量查詢指的是一次查詢結果超過十萬行。
  • Amoeba需要更嚴格的SQL語句規範
    From 關鍵字後面如果不是子查詢，一律不能帶括號」()」；
    如果的表中字段名與關鍵字或者函數名一樣需要帶上字符` （比如：mytable.`order`）

  • 與直接操作mysql數據庫相比，amoeba性能的損失大概在怎麼一個範圍？
    amoeba是屬於代理層，起碼要消耗2次網絡IO，加上amoeba自身的計算時候的消耗，
    如果數據庫的性能非常高，或者sql的執行並不消耗數據庫的磁盤IO，那麼這時候完全不能體現amoeba的好處，反而會降低整體的性能。具體數據目前沒有算過。

結論

• 學習曲線
  由於MySQL Proxy需透過Lua Script
  看起來不難，不過要初學者在linux去碰這東西，還真是有難度
  
  而Amoeba設定就簡單的多
  只需要進行相關的XML配置就可以滿足需求
  用SQLJEP語法寫規則
  雖然lua也不難，但比起設定檔的學習曲線還是有差
• 效能方面
  比官方的mysql proxy性能大致低10%~20%左右
  在一次請求數據量比較大的情況下比如好幾千條數據，性能有所下降,這個原因是由於解析了mysql與客戶端之間交互的所有數據包，而mysql proxy在沒有使用lua特定腳本的時候是不會解析mysql數據包
  並發能力來說比mysql proxy會更強勁,還有穩定性方面都比mysql proxy強。從使用的連接數來看，在相同的客戶端並發連接情況下，與mysql數據庫的連接數將比mysql proxy少一半。
  在0.27版本以後，amoeba的性能逐漸超過了MySQL Proxy
• 文件支援
  維護amoebad的人似乎不多
  在官網查相關文件都有些不清楚
  問題回覆也沒有歸類，有問題不好解決
• 穩定性
  覺得Amoeba太新了
  官網上也有不少人回應bug的事
  穩定性也是需要考量的地方
References
• Amoeba 跟 mysql proxy的区别.
• MySQL+amoeba，輕量級的分佈式數據庫實現
• MySQL使用Amoeba作为Proxy时的注意事项
• MySQL load balancing and read-write splitting with MySQL Proxy 
• Amoeba for mysql 0.31发布（读写分离、负载均衡、Failover、数据切分
  

MySQL - select最佳化分析

利用EXPLAIN幫助索引和查出最佳化的查詢語法
EXPLAIN SELECT * FROM website WHERE url='http://homeserver.com.tw';

欄位資訊，完整版看官網EXPLAIN的欄位說明

Column	Meaning
id	The SELECT identifier
select_type	The SELECT type 沒有union為simple, 其他看官網
table	The table for the output row
type	The join type 最優至最差的類型為system,const, eq_reg, ref, range, ..., ALL
possible_keys	The possible indexes to choose
key	The index actually chosen 如果為NULL，則是沒有使用索引。
key_len	The length of the chosen key 長度越短 準確性越高。
ref	The columns compared to the index 顯示那一列的索引被使用。一般是一個常數(const)。
rows	Estimate of rows to be examined
Extra	Additional information MySQL用來解析額外的查詢訊息。如果此欄位的值為：Using temporary和Using filesort，表示MySQL無法使用索引。

• Extra
  Extra為MySQL用來解析額外的查詢訊息，其中欄位值所代表的意義如下：
• Distinct：當MySQL找到相關連的資料時，就不再搜尋。
• Not exists：MySQL優化 LEFT JOIN，一旦找到符合的LEFT JOIN資料後，就不再搜尋。
• Range checked for each Record(index map:#)：無法找到理想的索引。此為最慢的使用索引。
• Using filesort：當出現這個值時，表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。
• Using index：返回的資料是從索引中資料，而不是從實際的資料中返回，當返回的資料都出現在索引中的資料時就會發生此情況。
• Using temporary：同Using filesort，表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果，此情況會發生在針對不同的資料進行ORDER BY，而不是GROUP BY。
• Using where：使用WHERE語法中的欄位來返回結果。
• System：system資料表，此為const連接類型的特殊情況。
• Const：資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行，這個值就是常數，因為MySQL會先讀這個值然後把它當做常數。
• eq_ref：MySQL在連接查詢時，會從最前面的資料表，對每一個記錄的聯合，從資料表中讀取一個記錄，在查詢時會使用索引為主鍵或唯一鍵的全部。
• ref：只有在查詢使用了非唯一鍵或主鍵時才會發生。
• range：使用索引返回一個範圍的結果。例如：使用大於>或小於<查詢時發生。 index：此為針對索引中的資料進行查詢。 ALL：針對每一筆記錄進行完全掃瞄，此為最壞的情況，應該儘量避免。

Reference

• MySQL最佳化分析指令EXPLAIN

mysql datetime format

• 常用格式
• 2011-03-28
  DATE_FORMAT(NOW(), '%Y-%m-%d');
• DATE_FORMAT(date,format)

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英語前綴的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 數字, 4 位
%y 年, 數字, 2 位
%a 縮寫的星期名字(Sun……Sat)
%d 月份中的天數, 數字(00……31)
%e 月份中的天數, 數字(0……31)
%m 月, 數字(01……12)
%c 月, 數字(1……12)
%b 縮寫的月份名字(Jan……Dec)
%j 一年中的天數(001……366)
%H 小時(00……23)
%k 小時(0……23)
%h 小時(01……12)
%I 小時(01……12)
%l 小時(1……12)
%i 分鐘, 數字(00……59)
%r 時間,12 小時(hh:mm:ss [AP]M)
%T 時間,24 小時(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一個星期中的天數(0=Sunday ……6=Saturday ）
%U 星期(0……52), 這裡星期天是星期的第一天
%u 星期(0……52), 這裡星期一是星期的第一天
%% 一個文字「%」。

Reference
Mysql日期和时间函数不求人

MySQL 資料庫備份與還原

• 備份
  //將DB1輸出到DB1.sql
#mysqldump -u root -p DB1 > DB1.sql
  
• 還原
  
  • 還原整個資料庫
    #mysql -u root -p DB1 < DB1.sql
  • 還原資料表 (其實一樣)
    #mysql -u root -p DB1 < DB1_Table1.sql

Reference
MySQL 資料庫的備份與還原

MySQL效能 - 欄位

varchar + utf-8 長度設超過 170 的話, 就無法存在 memory 裡。別以為用 varchar 就可以亂設最長長度啊。 ...截至 fcamel MySQL varchar 長度和效能問題

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

幾個會引起的問題

• host位置空白
  連mysql時，如果host位置為空
  也會出現這個錯誤
• 硬碟滿了
  今天開了aptana在寫程式，網頁似乎沒反應剛寫的東西
  說沒有，但偶爾又有東西反應，想說是不是有什麼東西卡住了
  重開機後，還是一樣，甚至寫的東西居然都存不起來
  存了，但再開會不見，想說從沒遇過這問題
  
  還在查問題時，發現db掛了
  問題是Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
  上網查了一下... 試了一下
  不然依然沒解決, 試了好久，都還是失敗
  
  一問同事，同事也曾發生過，說是不是硬碟滿了
  df一下.. 嗯 還真的是咧.. 硬碟用量100%
  因為是用vm... 只切了8gb...
  才回想到上午傳了1g多的東西進vm
  難怪傳到一半 就一直說寫入失敗
  檔案刪一刪後 就ok了... 呼 幸好解決了

Reference
發生找不到 mysql.sock 的處理方法!

log查詢速度時間過長的query (slow log)

想要知道哪些指令,Query資料會過慢的方法:
自動記錄並log出這樣的資料可以在資料庫慢時幫助抓問題
設定檔=/etc/my.cnf

加入以下的資料到my.cnf內
log-slow-queries = /var/log/mysql/mysql-slow.log (記錄檔的位置可以自己改)
long_query_time = 1 (超過設定的秒數就記錄1代表一秒2代表兩秒)
log_long_format

注意寫入權限問題
如果沒看到記錄檔可以自己先在這個目錄下建立個空白的檔案
並設定可以寫入的權限,這樣才可以記錄到檔案



Reference
mysql_自動log哪些Query速度慢時間過長的指令

MySQL常用指令

• 將db輸出 (console mode)
  

  mysqldump  -u root -p [dbname] > [dbname].sql

• 覆蓋db (console mode)
  

  mysql -u root -p [dbname]  < [dbname].sql

• 指定資料 匯入/匯出 (進入MySQL執行)
  

  //匯出
  select * into outfile '/mdr/data/UNLOAD/檔名.sql' from [TableName]  where ITEM='409667';
  //匯入
  load data infile '/mdr/data/UNLOAD/檔名.sql' into table [TableName]

• 匯入/匯出 整個Table (console mode)
  

  //匯出
  mysqldump -r(權限) 檔名.sql -u [帳號] -p --default-character-set=big5 [dbname]  [TableName]
  //匯入
  mysql -u [帳號] -p --default-character-set=big5 [dbname]  < [檔名].sql

• 輸出為mssql 格式
  

  mysqldump --compatible=mssql -u root -p phalcon > ~/phalcon_test.sql

p.s. 感謝雅湘學妹提供

mysql 權限管理

管理可連線的Host及其帳戶
透過PhpMyAdmin來做 卻一直失敗
本以為是PhpMyAdmin flush privileges有問題
但試完後，還是失敗
後來改用console模式進去做 就成功了...

範例從references2抄來的
允許 someone 從任何主機連入
mysql> grant usage on *.* to someone@'%' identified by 'passwd';
mysql> flush privileges;
mysql> exit
p.s. 記得開放相關table讀取權限，不然又讀不到

References

1. Specifying Account Names 
2. mysql 權限管理備忘

忘記 mysql 密碼

[MYSQL] 忘記 root 密碼，登不進去 phpMyAdmin 教學

常見問題

*Access denied for user 'kevyu'@'' (using password: YES)
ans: mysql 未設定此帳號kevyu具有remote，從遠端登入的權限

取得last insert id

MySQL新增完後
會將資料留在DB Connection

PHP--
PHP有提供相關函數
執行mysql_insert_id()，即最後新增的id


.Net-- (http://forums.mysql.com/read.php?38,98672,98868#msg-98868)
string strSQLSelect = "SELECT @@IDENTITY AS 'LastID'";
MySqlCommand dbcSelect = new MySqlCommand(strSQLSelect, conDBConnection);
MySqlDataReader dbrSelect = dbcSelect.ExecuteReader();

dbrSelect.Read();
int intCounter = Int32.Parse(dbrSelect.GetValue(0).ToString());

dbrSelect.Dispose();
conDBConnection.Close();
MessageBox.Show("Test: " + intCounter.ToString());

MySQL中的DATETIME, DATE和TIMESTAMP類型

DATE跟DATETIME就如字意上 只差TIME
而DATETIME跟TIMESTAMP是存相同的內容
網路上看的差別在
一個UPDATE設置一個列為它已經有的值，這將不引起TIMESTAMP列被更新

reference
DATETIME, DATE和TIMESTAMP類型

MySQL亂碼

在網路上找半天
原因是MySQL預設文字編碼是 cp1252 West European (latin1)
因此儲進去的格式已變成latin1了 所以用其他編碼讀取來當然會錯

最簡單的解法方法
1.網頁上 (如phpMyAdmin)
=>讓資料庫的儲存格與讀取的編碼相同
1.將文字編碼cp1252 West European (latin1) 改為utf8
2.sql connection string =>Host=localhost;Database=xxx;charset=utf8
加上編碼utf8即可正常顯示

至於原本已經存latin1的...
還沒找到解決方法


另外利用連線取資料
如果資料庫設為utf，則建利連線後
可下 mysql_query("SET CHARACTER SET 'utf8'");