mysqlで更新があったレコード数を取得

mysql で更新があったレコード数を取得する方法。

各種言語のライブラリだと、mysql_affected_rows() 的なもので更新が合った行数を取得できますが、
mysql のコマンドラインでは、更新を行った直後に select row_count() を実行します。

以下の様なテーブルを作成。

create table ids (
       id    integer not null,
       primary key(id)
);

insert into ids set id = 1;
insert into ids set id = 2;
insert into ids set id = 3;
insert into ids set id = 4;
insert into ids set id = 5;
insert into ids set id = 6;
insert into ids set id = 7;
insert into ids set id = 8;
insert into ids set id = 9;
insert into ids set id = 10;

変更があった行数を取得。

mysql> delete from ids where id <= 4;
Query OK, 4 rows affected (0.01 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> delete from ids where id <= 6;
Query OK, 2 rows affected (0.00 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysqlのnow()の更新時刻

mysqlでトランザクションを使用した場合のnow()の更新時刻を調べてみました。
トランザクションを使用した場合、now()の時刻はcommitの時刻ではなく、updateを受理した時刻になるようです。


mysql> create table test1 (
-> t datetime
-> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 set t = now();
Query OK, 1 row affected (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-11-13 23:26:29 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
1 row in set (30.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+---------------------+
| t |
+---------------------+
| 2011-11-13 23:26:16 |
+---------------------+
1 row in set (0.00 sec)

mysql の Lock wait timeout exceeded エラー

mysql で複数のプロセスから同時にテーブルを更新しようとした場合に、Lock wait timeout exceeded エラーが発生する場合があります。

タイムアウトするまでの秒数は、innodb_lock_wait_timeout で設定されている秒数となります。
デフォルトでは50秒のようです。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+

mysqldumpが使えない場合のデータダンプ方法

mysqldump では -w オプションでダンプするレコードの条件を指定することができますが、例えば複数のテーブルを組み合わせた条件を指定することはできません。
他にも view の実データをダンプしたい場合に、mysqldump で view を指定すると、view のテーブル定義がダンプされるだけです。

このような場合に、select 文で insert/replace 文を生成するのはいかがでしょうか。

mysql> select * from test_dump;
+---------+---------+
| int_col | str_col |
+---------+---------+
| 1 | abc |
| 2 | a'b'c |
+---------+---------+

$ cat dump.sql
select
concat(
"replace into test_dump set"
, " int_col = ", int_col
, ", str_col = ", quote(str_col)
, ";")
from
test_dump
;

$ cat dump.sql | mysql -s DB名
replace into test_dump set int_col = 1, str_col = 'abc';
replace into test_dump set int_col = 2, str_col = 'a\\'b\\'c';


quote() を使えば、文字列を ' で囲んで、エスケープが必要な文字をエスケープして出力してくれます。

mysqlで文字列のキャラクタセットを変更する方法

mysqlで文字列のキャラクタセットの違いによるエラーが発生した場合の対処方法です。

テーブルのキャラクタセットが ascii で、文字列のキャラクタセットが utf8 の場合、strcmp('http://test.com/', url) とすると、エラーがでます。

`url` varchar(220) character set ascii collate ascii_bin default NULL,


mysql> select charset('http://test.com/');
+-----------------------------+
| charset('http://test.com/') |
+-----------------------------+
| utf8 |
+-----------------------------+


Illegal mix of collations (utf8_general_ci,COERCIBLE) and (ascii_bin,IMPLICIT) for operation 'strcmp'


この場合、cast() で文字列のキャラクタセットを変更することで、エラーがでなくなります。

strcmp(cast('http://test.com/' as binary), url)

または、文字列のキャラクタセットを _キャラクタセット で指定します。

strcmp(_binary 'http://test.com/', url)

mysqldumpでレプリケーション用にデータをダンプする方法

mysqlでレプリケーションのスレーブ用にデータをダンプするのに、mysqldumpコマンドで--master-dataを指定すると、ダンプファイルにレプリケーション位置を指定するmaster_log_file、master_log_posを設定するSQLも出力されます。

$ mysqldump --master-data DB名 テーブル名

出力ファイルには以下のように master_log_file と、master_log_pos を指定するSQLが出力されています。

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='.000650', MASTER_LOG_POS=458955379;

rubyのString#splitで分割文字列も取得する方法

rubyのString#splitの第1引数に指定する分割パターンは、通常は戻り値の配列に含まれません。

戻り値の配列で分割パターンを取得するには、分割パターンを(...)で指定します。

irb(main):001:0> "abc,def,ghi".split(/(,)/)
=> ["abc", ",", "def", ",", "ghi"]

mysqldumpでテーブルをロックしながらダンプする方法

mysqldumpでテーブルをダンプする際に、--lock-all-tables オプションを指定すると、すべてのテーブルをリードロックした状態でダンプを行います。

$ mysqldump --lock-all-tables db table1 table2

ロック対象は、全DBの全テーブルです。
mysqldumpの対象ではないテーブルへの書き込みはブロックされます。

そのため、レプリケーションの設定を行う際に、マスターのデータをダンプしてスレーブに登録するような場合には、--lock-all-tables を指定してダンプするのがよさそうです。


--lock-tables オプションの場合には、指定されたテーブルがダンプされている時間帯だけリードロックされるようです。

$ mysqldump --lock-tables db table1 table2

table1 をダンプしている間は、table2 への書き込みが可能です。

mysql でテーブルの更新時刻を取得する方法

mysql でテーブルの更新時刻は、information_schema DB の tables テーブルの UPDATE_TIME で取得することができます。

mysql> select table_schema, table_name, update_time from information_schema.tables where table_schema = 'DB名' and table_name = 'テーブル名'\G
+--------------+------------+---------------------+
| table_schema | table_name | update_time |
+--------------+------------+---------------------+
| DB名 | テーブル名 | 2011-05-27 20:52:59 |
+--------------+------------+---------------------+
1 row in set (0.01 sec)

MySQLでテーブルのチェックサムをとる方法

checksum table テーブル名 でテーブルのチェックサムをとることができます。

以下のようなテーブル、データを登録します。

create table test1 (
id int,
name varchar(80)
);

insert into test1 set id = 1, name = 'id1';
insert into test1 set id = 2, name = 'id2';
insert into test1 set id = 3, name = 'id3';
insert into test1 set id = 4, name = 'id4';
insert into test1 set id = 5, name = 'id5';

$ mysql
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | id1 |
| 2 | id2 |
| 3 | id3 |
| 4 | id4 |
| 5 | id5 |
+------+------+
5 rows in set (0.00 sec)

mysql> checksum table test1;
+-------------------+------------+
| Table | Checksum |
+-------------------+------------+
| test_nikeda.test1 | 1155597644 |
+-------------------+------------+
1 row in set (0.00 sec)


チェックサムの値は、テーブルに登録するデータの順番に影響されないようです。
以下の順にデータを登録します。

insert into test1 set id = 1, name = 'id1';
insert into test1 set id = 3, name = 'id3';
insert into test1 set id = 5, name = 'id5';
insert into test1 set id = 2, name = 'id2';
insert into test1 set id = 4, name = 'id4';

mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | id1 |
| 3 | id3 |
| 5 | id5 |
| 2 | id2 |
| 4 | id4 |
+------+------+
5 rows in set (0.00 sec)

mysql> checksum table test1;
+-------------------+------------+
| Table | Checksum |
+-------------------+------------+
| test_nikeda.test1 | 1155597644 |
+-------------------+------------+
1 row in set (0.00 sec)

データの登録順には関係なく、チェックサムの値は一致しています。

mysqldumpでデータのみ/テーブル定義のみをダンプする方法

mysqldumpをオプションなしで実行すると、標準出力にテーブル定義のcreate文とinsert文が出力されます。

insert文だけを出力するには、-t または --no-create-info オプションを指定します。
$ mysqldump -t DB名 テーブル名

テーブル定義だけを出力するには -d または --no-data オプションを指定します。
$ mysqldump -d DB名 テーブル名

テーブル定義だけを出力する方法として、-w でダンプするデータの条件が偽になるようにするという手もあります。
$ mysqldump DB名 テーブル名 -w '0=1'

さらに意味のない方法ですが、show create table を使って create 文を出力することもできます。
show create table は以下のようにテーブル定義を出力します。

mysql> show create table id;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| id | CREATE TABLE `id` (
`id` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-s でカラム名を抑制し、-r でエスケープ文字をそのまま出力するようにして、2カラム目だけを cut コマンドで出力します。
$ echo "show create table テーブル名;" | mysql DB名 -s -r
CREATE TABLE `id` (
`id` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysqldump -d を使えば、こんな面倒なことはしなくていいんですが。

mysqlコマンドの検索結果で文字列をエスケープしない方法

mysqlコマンドの標準入力に select 文を流すと、テキスト系カラムの検索結果は、文字列がエスケープされます。

$ cat sel.sql
select * from txt;

$ cat sel.sql | mysql test
txt
abc\tdef
1\n2\n3\n

検索結果の文字列がエスケープされないようにするには mysql コマンドに -r または --raw を指定します。

$ cat sel.sql | mysql -r test
txt
abc def
1
2
3

mysqlでテーブルのレコード数を確認する方法

mysqlでテーブルのレコード数を取得する方法です。
select count(*) from テーブル名 でもよいのですが、たとえばDBの全テーブルのレコード数を知りたい場合には、テーブル数だけ select を実行しなければならなくなります。

こんな場合には、mysqlのテーブル情報でも書いた information_schema DB の TABLES テーブルから情報を取得します。

> select table_name, table_rows from information_schema.TABLES;

+------------+------------+
| table_name | table_rows |
+------------+------------+
| tbl1 | 10 |
| tbl2 | 5 |
| tbl3 | 5 |
+------------+------------+
3 rows in set (0.00 sec)

これだと全DBのテーブル情報を取得してしまうので、特定のDBのテーブルのレコード数を取得するには、where table_schema = 'DB名' で対象とする DB を指定します。

select table_name, table_rows from information_schema.TABLES
where table_schema = 'DB名';

mysqlのgroup_concat

mysqlで、行毎の文字列データを連結するには、group_concat を使います。

■テーブル定義
drop table if exists id;
create table id (
id varchar(255)
);

insert into id set id = 'id1';
insert into id set id = 'id2';

drop table if exists score;
create table score (
id varchar(255),
subject varchar(16),
score int
);

insert into score set id = 'id1', subject = 'math', score = '90';
insert into score set id = 'id1', subject = 'sci', score = '80';
insert into score set id = 'id1', subject = 'eng', score = '70';
insert into score set id = 'id2', subject = 'math', score = '100';

■select文
select
i.id as id
, group_concat(s.subject, ',', s.score order by subject separator ',') \
as scores
from
id i
, score s
where
i.id = s.id
group by
i.id
order by
i.id
;

■検索の実行結果
id scores
id1 eng,70,math,90,sci,80
id2 math,100

mysqlで検索結果の上位に対して、さらに条件指定

mysqlで検索結果を何らかの順序でソートした結果に対して、さらに条件を指定して検索結果を絞り込む方法です。

mysql> select * from tbl3;
+-----+------+-------+
| id | type | score |
+-----+------+-------+
| id0 | O | 0 |
| id1 | B | 2 |
| id2 | A | 4 |
| id3 | AB | 6 |
| id4 | A | 8 |
+-----+------+-------+
5 rows in set (0.00 sec)


scoreの上位3件内で、type='A'のレコードだけを抽出するには
以下のようにします。
ordにはscoreでの順位を取得しています。

mysql> set @ord := 0;
mysql>
select
t.ord
, t.id as id
, t.type as type
, t.score as score
from
(
select
@ord := @ord + 1 as ord
, id as id
, type as type
, score as score
from
tbl3
order by
id desc
limit 3
) t

where
type = 'A'
;

+------+-----+------+-------+
| ord | id | type | score |
+------+-----+------+-------+
| 1 | id4 | A | 8 |
| 3 | id2 | A | 4 |
+------+-----+------+-------+
2 rows in set (0.00 sec)