mysql order by null

2013年8月11日 沒有評論

order by null用途是強制對查詢結果禁用排序。通過explain檢查查詢語句時候,如果看到Extra列中有Using filesort,這是對性能有一定影響的,特別是使用了group by時,就算你沒有顯示的指定order by,mysql會默認按照分組字段進行排序。某些情況下是沒有必要使用排序的,例如在處理表報數據的時候(把原始表數據統計后插入到一個用于報表查詢的表),則完全可以可以使用order by null來取消排序。如:

insert into reportTable(day, clicks, revenue)
    select day, count(*), sum(revenue) from clickOriginTalbe
    group by day
    order by null
分類: MySQL 標簽:

通過命令行執行mysql命令

2013年8月5日 沒有評論

有兩種方法可以在命令行下執行sql命令。第一種是使用“-e“參數來指定需要執行的sql語句;第二種是通過管道的方式。語法及例子如下:

1、
MYSQL_HOME/bin/mysql -u用戶名 -p密碼 -D數據庫名 -e"sql 語句"
/usr/local/mysql/bin/mysql -uroot -p123456 -Dmysql -e"select host,user from user";

2、
echo "sql 語句" | MYSQL_HOME/bin/mysql -u用戶名 -p密碼 -D數據庫名
echo "select host,user from user" | /usr/local/mysql/bin/mysql -uroot -p123456 -Dmysql

上面命令執行的結果如下圖。從圖中可以看到二者的顯示結果有些不一樣。

使用場景多用在腳本中,例如定期執行存儲過程、load data infile以及刪除老數據等。

分類: Linux, MySQL 標簽:

mysql partition 功能

2013年7月30日 沒有評論

自mysql 5.1.3起開始支持分區功能。mysql表中存儲的記錄和表對應的索引信息,最后都是以文件的方式存儲在計算機的硬盤上的,有了分區功能,就可以按照一定的分區規則把這些記錄分布到不同的數據文件上,目前分區規則有四種,分別是RANGE、LIST、HASH和KEY,另外通過DATA DIRECTORY和INDEX DIRECTORY選項可以把不同的分區數據文件分散到不同的磁盤上,從而進步一提高系統的IO吞吐量。因此按照業務邏輯設計好了分區,可以大大提高查詢效率,而且刪除數據可能也會很容易。但是分區也有一些限制:1、主鍵或者唯一索引必須包含分區字段;2、只能通過int類型的字段或者返回int類型的表達式來分區;3、單表最多只能有1024個分區。

默認mysql是開啟了分區功能的,可以通過下述查詢查看結果:

show variables like '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+

//YES 表示開啟

下面也range規則為例介紹一下分區常用的命令。

1、創建分區。可以在創建表的同時創建,也可以在表創建后追加分區。

drop table if exists `netingcn_com`;
create table `netingcn_com` (
  `id` int(11) unsigned not null auto_increment,
  `day` int(11) not null default 0,
  primary key (`id`, `day`)
) engine=innodb default charset=utf8 auto_increment=1;

alter table `netingcn_com` partition by range(`day`) (
  partition p_2012 values less than (20130000),
  partition p_2013 values less than (20140000)
);
或
drop table if exists `netingcn_com`;
create table `netingcn_com` (
  `id` int(11) unsigned not null auto_increment,
  `day` int(11) not null default 0,
  primary key (`id`, `day`)
) engine=innodb default charset=utf8 auto_increment=1 
 partition by range(`day`) (
  partition p_2012 values less than (20130000),
  partition p_2013 values less than (20140000)
);

可以explain命令查看分區是否創建成功
explain partitions select * from netingcn_com where day = 20130412;
+----+-------------+--------------+------------+-------+
| id | select_type | table        | partitions | type  |
+----+-------------+--------------+------------+-------+
|  1 | SIMPLE      | netingcn_com | p_2013     | index |
+----+-------------+--------------+------------+-------+

2、增加或刪除分區。注意:刪除分區的同時,該分區的所有數據也會別刪除。

增加分區
alter table netingcn_com add partition (
  partition p_2014 values less than (20150000)
);

刪除分區
alter table netingcn_com drop partition p_2012;

3、重新分區。注意:hash和key分區規則不能用REORGANIZE來重新分區

alter table netingcn_com reorganize partition p_2013,p_2014 into (partition p_2014 values less than (20150000));

更多關于mysql 分區功能可以參考:http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

分類: MySQL 標簽: , ,

mysql 清除relay-log文件

2013年7月22日 沒有評論

今天在本機的mysql數據目錄下發現了許多類似hostname-relay-bin.0000*的文件,該文件一般是在mysql slave實例上存在。主要用途是記錄主從同步的信息,正常情況下會自動刪除的。

本機未配置過master、slave,對于其來源還真不太清楚。既然是用在slave上的,那就可以放心的刪除。刪除master實例上的日志文件用reset master,對于slave實例就使用命令:

reset slave
分類: MySQL 標簽:

mysql 關閉 bin-log 日志

2013年7月22日 1 條評論

當開啟bin-log日志時,會生成很多mysql-bin.0000*類似的文件,而且可能會占用很大的硬盤空間。對于自己的測試機器或硬盤比較緊張的vps,而又不需要做master,slave的配置,完全可以關閉日志功能。

關閉操作很簡單,找到配置文件my.cnf,對于linux,一般默認在/etc目錄下,打開此文件,使用井號(#)注釋掉如下兩個配置項目即可。

log-bin=mysql-bin
binlog_format=mixed

配置修改好后需要重啟mysqld服務才能生效。可能在重啟時候會收到一個錯誤,“ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER”,解決辦法是先登入到mysql命令行中執行 reset master 即可。reset master的用途就是刪除先前所以的bin-log日志文件。所以在master、slave配置環境中慎用此命令。

分類: MySQL 標簽:

mysql 查看數據庫中所有表的記錄數

2013年3月3日 沒有評論

mysql使用select count(*) from table_name可以查詢某個表的總記錄數。想快速的知道數據庫中所有表的記錄數信息怎么辦?如果使用mysql的版本在5.0及以上,可以通過查詢information_schema庫中的tables表來獲取,該表中使用table_rows記錄表的行數信息。例如查看庫testdb中所有表的記錄數:

use information_schema;

select table_name,table_rows from tables 
where TABLE_SCHEMA = 'testdb' 
order by table_rows desc; 

不過需要注意的是,對于InnoDB表,table_rows行計數僅是大概估計值。

另外一種辦法還是借助information_schema庫的tables表,來拼接出一個條sql語句,例如:

use information_schema;

select concat(
    'select "', 
    TABLE_name, 
    '", count(*) from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all'
) from tables 
where TABLE_SCHEMA='testdb';

把生成的結果手動加工一下就行了,起碼比一張張表去拼寫要來的快。

mysql date_add date_sub

2013年1月18日 沒有評論

mysql中內置函數date_add和date_sub能對指定的時間進行增加或減少一個指定的時間間隔,語法如下:

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)

其中date是指定的日期,INTERVAL為關鍵詞,expr是具體的時間間隔,type是時間單位。注意:type可以復合型的,比如YEAR_MONTH。如果type不是復合型的,DATE_ADD和DATE_SUB其實可以通用,因為expr可以為一個負數。可用的type如下表:

MICROSECOND 間隔單位:毫秒
SECOND 間隔單位:秒
MINUTE 間隔單位:分鐘
HOUR 間隔單位:小時
DAY 間隔單位:天
WEEK 間隔單位:星期
MONTH 間隔單位:月
QUARTER 間隔單位:季度
YEAR 間隔單位:年
SECOND_MICROSECOND 復合型,間隔單位:秒、毫秒,expr可以用兩個值來分別指定秒和毫秒
MINUTE_MICROSECOND 復合型,間隔單位:分、毫秒
MINUTE_SECOND 復合型,間隔單位:分、秒
HOUR_MICROSECOND 復合型,間隔單位:小時、毫秒
HOUR_SECOND 復合型,間隔單位:小時、秒
HOUR_MINUTE 復合型,間隔單位:小時分
DAY_MICROSECOND 復合型,間隔單位:天、毫秒
DAY_SECOND 復合型,間隔單位:天、秒
DAY_MINUTE 復合型,間隔單位:天、分
DAY_HOUR 復合型,間隔單位:天、小時
YEAR_MONTH 復合型,間隔單位:年、月

對應復合型的type,需要使用引號對兩個參數進行引用起來,中間用任何非數字字符作為間隔即可,并且不能使用負數。但是時間間隔只指定了一個值,那么也能正常工作,但是對應XXX_YYY使用的單位為YYY,也就相當于單一單位的type,同時可以使用負數。復合型的用法如下:

mysql> select date_add('2013-01-18', interval '1 2' YEAR_MONTH);
+-----------------------------------------------------+
| date_add('2013-01-18', interval '1 2' YEAR_MONTH) |
+-----------------------------------------------------+
| 2014-03-18                                          |
+-----------------------------------------------------+

mysql> select date_add('2013-01-18', interval '1-2' YEAR_MONTH);
+----------------------------------------------------+
| date_add('2013-01-18', interval '1-2' YEAR_MONTH) |
+----------------------------------------------------+
| 2014-03-18                                         |
+----------------------------------------------------+

mysql> select date_add('2013-01-18', interval '1,2' YEAR_MONTH);
+---------------------------------------------------+
| date_add('2013-01-18', interval '1,2' YEAR_MONTH) |
+---------------------------------------------------+
| 2014-03-18                                        |
+---------------------------------------------------+

mysql> select date_add('2013-01-18', interval 1 YEAR_MONTH);
+-----------------------------------------------+
| date_add('2013-01-18', interval 1 YEAR_MONTH) |
+-----------------------------------------------+
| 2013-02-18                                    |
+-----------------------------------------------+

mysql> select date_add('2013-01-18', interval -1 YEAR_MONTH);
+------------------------------------------------+
| date_add('2013-01-18', interval -1 YEAR_MONTH) |
+------------------------------------------------+
| 2012-12-18                                     |
+------------------------------------------------+
分類: MySQL 標簽: ,

mysql ifnull

2013年1月17日 沒有評論

mysql內置的ifull函數可以用在查詢時候為NULL值字段給一個默認值,例如:

select ifnull(col1, 'default-value'), col2 from test;

當test表的col1字段為NULL時,數據庫返回的結果就為default-value,否則就返回本身的值。但是當col1字段的值為空字符串(”),由于空字符不是NULL,因此返回的結果還是空字符串。如果需要把空字符串或NULL值都用default-value代替,顯然ifnull是不行,不過使用case when語句能搞定,示例如下:

select c1,
(case when c2 = '' or c2 is null then 'default-value' else  c2 end) 
from test;
分類: MySQL, 數據庫 標簽:

Mysql設置自增長主鍵的初始值

2012年11月23日 沒有評論

Mysql可以使用AUTO_INCREMENT來設定主鍵的值為自增長的,其默認值是1,如果想把它的初始值設置為1000,比較笨的辦法是先插入一條記錄并指定主鍵的值為999,然后delete改行記錄,例如:

insert into test(pk) values(999);
delete from test where pk = 999;

更好的方法是使用alter的方法來直接修改,例如:

alter table test AUTO_INCREMENT = 200;

mysql 語法 on duplicate key update

2012年11月23日 沒有評論

需要根據某個時間點來統計數量,例如統計每天網站的訪問量,本人以前的做法是先查詢當前有沒有記錄,如果存在就更新訪問量的值,否則就插入一條。其實在Mysql中可以一條SQL語句來搞定,只要使用ON DUPLICATE KEY UPDATE即可。例如:

insert into daypv(day,cnt) values('2012-11-22',1) 
     on duplicate key update cnt = cnt + 1;

如果insert 末尾指定了on duplicate key update,插入數據會導致一個primary key或unique索引出現重復,那么就對已經存在的行執行update操作,條件就是primary key或unique索引。

對于一次插入一條記錄沒有問題,如果一次插入多條記錄,其中某些可能會造成primary key或unique索引重復,那該如何處理呢。其實和單條處理類似,只要在后面使用values函數即可,例如:

insert into daypv(day,cnt)
	values('2012-11-22',1),
	('2012-11-23',2),
	('2012-11-24',3),
	('2012-11-25',4)
	on duplicate key update cnt = cnt + values(cnt);

上述 “cnt = cnt + values(cnt)”中第一個是cnt指更新的字段,第二個指原來的值,第三個是上面insert對應行中指定的cnt的值,例如2012-11-24記錄已經存在,cnt的原始值為5,那么執行上述sql后,2012-11-24的cnt就為8 ( 3 + 5)。

另外需要注意的是 ON DUPLICATE KEY UPDATE 是Mysql特有的,不是標準的SQL語法。

分類: MySQL 標簽:

無覓相關文章插件,快速提升流量

30选5怎么中奖