トップ  > メモ一覧  > カテゴリ「知識.ユーティリティ」の絞り込み結果 : 42件

42件中 1 〜 10 表示  1 | 2 | 3 | 4 ... 次の10件> 最後»

No.5234 mysql で位置情報を扱う

mysql に geometry型 という位置情報を扱う専用の型がある。
知らなかったので調べた結果をメモ。

### サンプルテーブル
~~~~
CREATE TABLE `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `latlng` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `latlng_idx` (`latlng`)   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
~~~~

### INSERT
~~~~
mysql> insert into location values (0, GeomFromText('POINT(35.6408657 139.6958103)'));
Query OK, 1 row affected (0.01 sec)
~~~~

### SELECT
~~~~
mysql> select X(latlng), Y(latlng), ASTEXT(latlng) from location;
+------------+-------------+-------------------------------+
| X(latlng)  | Y(latlng)   | ASTEXT(latlng)                |
+------------+-------------+-------------------------------+
| 35.6408657 | 139.6958103 | POINT(35.6408657 139.6958103) |
+------------+-------------+-------------------------------+
1 row in set (0.00 sec)
~~~~

### 注意点
* INSERT は GeomFromText('POINT( , )') を使用
* SELECT は ASTEXT() を使用
* X(), Y() で緯度・経度のみの取得も可能
* 位置情報の検索では geometory 型を使ったほうが速い
    + geometory を使うときはWHERE句で矩形検索で範囲を限定しないとインデックスは使われない
* 「SPATIAL KEY」を使うには、ストレージエンジンが MyISAM で、NOT NULL でないといけない。
* バイナリで保存されるので、mysqldump には --hex-blob オプションを使用する必要がある。
* geometry型の対応バージョンはMySQL5系から?(未確認)
* MySQL5.6 で位置情報を扱う便利関数が色々増えているらしい
    + Buffer() とか
        + 詳細は後で調べる

## 参考サイト
- http://d.hatena.ne.jp/IT7C/20100723/1279835242
- http://matty-studio.jp/post-30/
- http://phpjp.info/?Geometry%E5%9E%8B%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E3%80%81%E4%BD%8D%E7%BD%AE%E6%83%85%E5%A0%B1%E3%82%92%E4%BF%9D%E5%AD%98%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B
- http://qiita.com/halhide/items/396b3b63e95765d972c3
- http://blog.asial.co.jp/473
- http://qiita.com/kijtra/items/c8ca99de09dbe05c4470
- http://bugs.mysql.com/bug.php?id=43544

引用元

更新:2015/02/14 22:20 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.4560【引用】MySQLで意図的にSlow Query Logを発生させる方法

MySQLで意図的にSlow Query Logを発生させる方法
my.cnf

slow_query_log = ON
long_query_time = 1.000
log_output = TABLE
min_examined_row_limit= 100

とか設定して、

SELECT count ( * ) ,sleep ( 3 ) FROM hogehoge;

とか。
自分で作ったテーブルに対してやらないとSlow Query Logには残らないっぽい。

引用元

更新:2012/07/27 18:50 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.4157【引用】Oracle MySQL Developer Day – Tokyoに参加してきた。


Oracle MySQL Developer Day – Tokyoに参加してきた。
MySQL | 23:46 |
アジェンダ

10:00~12:00 MySQL エッセンシャル<技術概要>
12:00~13:00 昼食タイム
13:00~13:45 MySQL レプリケーション&スケーラビリティ
13:45~14:30 MySQL Cluster
14:30~14:45 休憩
14:45~15:30 MySQL パフォーマンス& チューニング 概要
15:30~16:15 MySQL 管理&運用
16:15~16:30 閉会

場所は青山の オラクル 本社。
初めて行きましたがとても立派な建物...

引用元

更新:2011/10/29 12:34 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.124【引用】dumpとバージョンの違い

mysqldump の仕様変更の弊害(文字化け) †
mysqldump は、3.X, 4.0 までは、コンパイルするときに指定されたキャラクターセットを標準としていました。
4.1 からは、mysqldump はコンパイル時のキャラクターセットを無視して、utf8 を標準とします。(ひどい。普通、mysqldump のキャラクターセットは、mysqlコマンドとかのキャラクターセットと同じだと思って使うよなぁ...)
 
これは、4.1 の文字コード自動変換機能と組合わさって、最低な状況を起こしてしまいます。
サーバーが ujis, sjis で動いていたとして...

引用元

更新:2011/08/05 22:24 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.3189 ソーシャルゲームのためのMySQL入門

こんにちはこんにちは。最近お腹痛いばっかり言ってることで有名なiwanagaです。

DeNAは外部的にはプラットフォーム的な部分の方がフィーチャーされることが多いですが、実はソーシャルゲームの提供も行っています。怪盗ロワイヤルとか、どこかで聞いたことがあるのではないでしょうか。

僕はDeNAでソーシャルゲームが誕生した辺りからずっとサーバサイドを見てきましたが、そんな運用の中で自分が貯めてきた知見とかTIPSをご紹介したいと思います。 かれこれ10タイトル近くはレビューしたり運用したりしてるため結構言いたいことはいっぱいあるので、小出しにしつつ評判よければ次も書きます。

「MySQLの話か、なんだアプリ開発の自分には関係ないな」と思ったそこのあなた!今回僕がこういう記事を書いている趣旨は、むしろ開発側の人たちにこそもっといいMySQLの使い方を知ってもらいたいと思ったからです。データストア層を使いこなせてこそ真のアプリ開発者ですよね!

その前に、なぜMySQL?

世の中流行りは「NoSQL」とか言われてるみたいですが、DeNAでは携帯ソーシャルゲームという高トラフィックなシステムでもデータストア層にはMySQLをヘビーに使っています。なぜでしょうか。

それは僕がMySQLが好きだからです

嘘です。すいませんすいません。真面目に書きます。MySQLにはなんといっても「輝かしい実績」があります。10年を数えるレプリケーション システム、高速・安全なトランザクショナルストレージエンジンInnoDB、そしてFacebookやGoogle、弊社のモバゲータウンをはじめとした 大規模サイトでの実績。

今では枯れた技術感のあるmemcachedですら、先般mixiさんが遭遇したようなクリティカルなバグがあったりしました。その意味では大 規模サイトでの実績充分、バクデータベースも豊富なMySQLは最も安定したデータストアであると言えます。NoSQLとかで話題になっているミドルウェ アは確かに新しく、適切な使い方をすれば非常に有効だと思います。しかし、安定感や使い方という意味でMySQL程に枯れたものは存在しません。さらに弊 社松信の言葉にもある様に「これから」も安心できるミドルウェアでもあります。

例えばその昔は「InnoDBは遅い」と言われていた時代もありましたが、今では十分に速く、弊社樋口が開発したHandlerSocket pluginの能力を見てもらえれば分かるとおり、うまく使えばInnoDBは十分に高速です。うまく使えば。。。

そう、この「うまく」の部分がなかなか難しいのです。特にソーシャルゲームは普通のSNSと比べて高トランザクションになりがちで、それでいて テーブルのサイズは猛烈な勢いで肥大化します。InnoDBを効率良く使いこなすためには、必然的にそのデータ構造を理解した上でスキーマを考えクエリを 考えていく必要があります。

というわけで前置きが長くなりましたが、今回はInnoDBをソーシャルゲームで使う時にどういう風にテーブルを作ったらいいのか、ということを少しだけお話したいと思います。(MySQLの設定とかの話はまた今度でー)

ソーシャルゲームのテーブルの特徴

MySQLの設定パラメータを変更するだけで高速にできることは限られています。何よりもスキーマとクエリを効率的にすることが重要です。ソー シャルゲームでよく用いられるテーブルにはどういうタイプのモノがあるかを考えてみましょう。全てではないですが、よくあるタイプはこんな感じです。(一 度でもRDBを使ってアプリ作ったことがない人には少々わかりにくいかも知れません><)

ユーザID単位で1つだけもつデータ

これは分かりやすいですね。例えばuser_idをプライマリーキーにして、ニックネームや経験値などのデータを保存する類です。

CREATE TABLE `user_data` (
 
`user_id` int(10) unsigned NOT NULL,
 
`nickname` varchar(40) NOT NULL,
...
  PRIMARY KEY
(`user_id`)
) ENGINE=InnoDB

ユーザID1つで複数もつデータ

これは例えばユーザの持っているアイテムのデータなどです。user_idとitem_idの複合キーがプライマリーキーになる感じです。

CREATE TABLE `user_item` (
 
`user_id` int(10) unsigned NOT NULL,
 
`item_id` int(10) unsigned NOT NULL,
 
`num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY
(`user_id`,`item_id`)
) ENGINE=InnoDB

ソーシャルグラフ的データ

これがソーシャルゲームならではのデータです。例えばあるユーザの友達のデータとか、あるユーザと別のユーザのやり取りを記録するデータとか。user_id-Aとuser_id-Bの複合キーがプライマリーキーになる様な感じです。

CREATE TABLE `user_relations` (
 
`user_id` int(10) unsigned NOT NULL,
 
`tgt_user_id` int(10) unsigned NOT NULL,
 
`visit_num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY
(`user_id`,`tgt_user_id`)
) ENGINE=InnoDB

ログ系のinsert中心のデータ

これは例えば何かのアクションの履歴データなどです。自分や他人からのアクションの履歴を表示したいときなどに活用すると思います。これは シーケンシャルな数字がプライマリーキーになり、それを他のテーブルに格納しておいたり、セカンダリインデックスで検索する形になると思います。

CREATE TABLE `action_log` (
 
`id` bigint(20) unsigned NOT NULL,
 
`date` timestamp NULL DEFAULT 0,
 
`value` int(10) unsigned NOT NULL,
...
  PRIMARY KEY
(`id`)
) ENGINE=InnoDB

パラメータなどのマスターデータ

これは敵の出現確率とかアイテムの名前とか、主に運営側のみが更新するデータです。こういうデータは別にRDBに保持する必要はなくて、 ファイルにまとめてアプリサーバにデプロイする運用もあると思いますが、バックエンドツールを使ってエンジニア以外の人でもパラメータが変更したいとかが ある場合にはRDBに保持している方が楽ちんだと思います。

 CREATE TABLE `item_conf` (
 
`item_id` int(10) unsigned NOT NULL,
 
`item_type` tinyint(3) unsigned NOT NULL,
 
`name` varchar(255) DEFAULT NULL,
...
  PRIMARY KEY
(`item_id`)
) ENGINE=InnoDB

意外と少ない!

もちろんこれら以外にいろんなデータはあると思いますが、多くのソーシャルゲームで共通しているのはここに当てはまるものが多いと思いますし、この辺のデータの扱い方が分かってくれば、これら以外のデータについても適切な扱い方のコツが分かってくると思います。

これらのテーブルについて、ソーシャルゲーム+InnoDBという組み合わせにおいてどういったアプローチを取るのがよいかという話をしたいのですが、全部やると多いので今回はまずログ系のテーブルについてフィーチャーしてみたいと思います。

ログ系テーブルの上手な使い方

いろんな欲求からログ系のテーブルを作りたくなることはしばしばあると思います。お金がらみのものが動くときの記録や、あるアクションの履歴をユーザ毎に見せたいとか。

「最近」のデータしか使わない工夫

ログ系のテーブルでは基本は新しくinsertされた行への参照がほとんどになる様に設計すべきです。何よりもこれが大事。ログ系のテーブ ルは放っておけばどんどんサイズが大きくなります。アクセスされるデータの量が昔のものまで満遍なく存在していると、buffer poolというメモリキャッシュに載せるべきデータが時間と共にどんどん増えていき、すぐにメモリから溢れてIOが発生します。IOは怖いです。まんじゅ う怖いとは違います。マジで怖いです。

これにはサービス的な割り切りが必要になることもあります。「2週間より前のデータは見れません」という仕様にしてしまえば、サービス的に2週間より前のデータは不要になるはずですが、「サービス開始時点から全て見れます」という仕様にしてしまうと、結構大変です。

下手にこんな仕様にしてしまうと、Twitterさんがやっている(と言っている)様に、いずれ時系列でサーバ自体を分ける様な運用が必要になってしまうでしょう。

とは言え、こういう背景を知らない人が普通の感覚で言ったら「全部見れて当たり前」という風になってしまうもの。ここはエンジニアが費用対効果を良く検討して、どういう仕様が適切なのかを考えてあげる必要があると思います。

次なる敵は「データサイズ」

さて、最近のデータだけあればよい、という設計ができたとしましょう。しかし、一度insertしてしまったデータは消さない限り基本的に はずっとDBには残り続けてしまいます。極端な例で言えばアクセスログの様に全PVで1レコードをこのテーブルに入れてしまうと、ソーシャルゲームの様に PVの多いサービスではあっという間に数億件とか数十億件とか、そういうサイズになってしまいます。

buffer pool的にはアクセスのないデータはメモリから追い出されるのであまり性能的な劣化はないかも知れません。しかしこのままでは物理的な「容量」がいずれ問題になってしまいます。DISKも無限ではありません。

そこでよくある方法としては、古いデータをdeleteで消す(パージする)というやり方です。先程も書いた様にこの手のテーブルで必要なデータは「最近」のものであることが多いので、思い切って消してしまうという戦略は当然アリです。ただし注意が必要で、deleteはクソ重いです。大切なので2回言います。deleteはクッソ重いです。ログ系のテーブルの場合、わざわざbuffer poolに乗ってないデータを読みだして消す必要があるため、ゲロ遅いです。

パーティションを使おう!

仕方ないので、MySQL 5.0の頃はパージするスクリプトを作って夜中のアクセスが少ない時間に毎日回してせっせと消すといった作業が必要でした。

しかし、MySQL 5.1からは念願の「パーティション機能」が追加されていますのでこれを使わない手はないでしょう。

パーティションを知らない人のために説明しますと、簡単に言えば1つのテーブルを「ある規則」に従って別のテーブルの如く格納させる仕組 み、と言えると思います。「ある規則」は色々設定できますが、データパージという意味でよく使うのは「RANGEパーティション」といって、あるカラムの 値の幅で切るというやり方です。言葉の説明だけだと分かりにくいですが、こんな感じ。

CREATE TABLE `action_log` (
 
`id` bigint(20) unsigned NOT NULL,
 
`date` timestamp NULL DEFAULT 0,
 
`value` int(10) unsigned NOT NULL,
  PRIMARY KEY
(`id`,`date`)
) ENGINE=InnoDB
PARTITION BY RANGE
(UNIX_TIMESTAMP(date))
(PARTITION p20101004 VALUES LESS THAN (UNIX_TIMESTAMP('20101004')),
 PARTITION p20101011 VALUES LESS THAN
(UNIX_TIMESTAMP('20101011')),
 PARTITION p20101018 VALUES LESS THAN
(UNIX_TIMESTAMP('20101018')),
 PARTITION p20101025 VALUES LESS THAN
(UNIX_TIMESTAMP('20101025')),
 PARTITION p20101101 VALUES LESS THAN
(UNIX_TIMESTAMP('20101101')),
 PARTITION p20101108 VALUES LESS THAN
(UNIX_TIMESTAMP('20101108')),
 PARTITION p20101115 VALUES LESS THAN
(UNIX_TIMESTAMP('20101115')),
 PARTITION over VALUES LESS THAN MAXVALUE
)

idはログのユニークな番号でこれについてはまた後述します。pYYYYMMDDというパーティションはYYYYMMDDまでのレコードが格納されるように設定されています。なおパーティションの細かい文法とかはMySQLのドキュメントを参考にして下さい。

一つ注意する必要があるのは、現在の仕様ではパーティションに切りたいカラム(↑ならdate)は全てのunique indexについてその一部である必要があります。そのため、action_logはidカラムだけで完全にユニークにできるのですが、敢えてPKに dateが入っています。

さて、こうしておくと例えば20101004のデータはもう不要なのでパージしたいと思ったときに、5.0時代は


delete from hoge_log where date < XXXXXXXX;

とか書いてたものを(本当はこんなの実行したら死にますが)


alter table hoge_log drop partition p20101004
;

とするだけで、あたかもdrop tableするかの如く、高速にパージすることができます。データが巨大なInnoDBを触ったことがないと実感がわかないかも知れませんが、これは本当 にありがたい機能です。もはやちまちまdeleteするスクリプトを書く必要もなく、スピード調整に失敗してレプリ遅延に泣かされることもなく(しかも大 抵夜中><!)、テーブルサイズをある程度のサイズで管理することができるようになるわけです。DBAにはヨダレものですね!

実はパーティションには他にも恩恵があります。それがプルーニング(刈り込み)と呼ばれる機能です。さっきのテーブルをselectする際 に、dateの条件を与えてあげると、場合によっては見る必要のないパーティションが出てくると思いますが、それをオプティマイザが判断してくれて必要な パーティションのみ見るようになってくれます。1週間単位の集計を行いたいとか、サービスからの参照は2週間前までのみでよいとかを明示的にwhere句 で与えてあげることで、より効果的にテーブルを使うことができます。

プルーニングが効いているかどうかは「explain partitions」を実行することで分かります。

mysql> explain partitions select * from hoge_log where id > XXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101004,p20101011,p20101018,p20101025,p20101101,p20101108,p20101115,over
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: *********
        Extra: Using where

mysql> explain partitions select * from hoge_log where date > XXXXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101108,p20101115,over
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: **************
        Extra: Using where

下のクエリではwhere句にdateを入れていて、刈り込みが効いていることが「partitions」のカラムを見ると分かりますね!

なんか通販みたいですが、パーティションにはさらにさらに特典があります。それは削除だけでなくINSERTについても性能が向上するとい うことです。前述の様にログ系のテーブルはINSERT中心になるのでこれもおいしい恩恵ですね。RANGEパーティションによるINSERT性能の改善 効果については、弊社松信の発表資料(英語ですが)も参考にしてください。http://www.mysqlconf.com/mysql2009/public/schedule/detail/6661(ここからたどれるPDFのp.44-46)

idはどうするか?

さて、ログ系のテーブルのもう一つのトピックとしては「id」をどうやって決めるか、というのがあります。素朴にやるなら、idカラムにauto_increment属性をつけてしまえば、MySQLが勝手にユニークなidをつけてくれるのでお手軽ですね。

ところが、規模が大きくなり秒間クエリ数が増大してくると困ったことになってきます。一つはMySQL 5.0以下のInnoDBを使っている場合には、弊社松信が書いているこちらのエントリにもあるように、並列性が低いという問題があります。こちらの解決の1つの方法はMySQL5.1以上のInnoDBを使うことで解決されます。

もう一つの大きな問題としては、このテーブルを2つ以上のデータベースに分割(Sharding)したくなった時です。この場合、複数データベースにまたがってユニークなidを確保するにはauto_incrementだけではちょっと面倒です。

そこでモバゲータウンではidだけを払い出すテーブルを1つ作って1レコードだけinsertして、updateにより採番し、実際のテーブルには採番したidを指定してinsertするという手段を用いています。

CREATE TABLE `seq_log` (
 
`id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM
INSERT INTO seq_log VALUES
(0);
my $sth = $dbh->prepare_cached("update seq_log set id=LAST_INSERT_ID(id+1)");
$sth
->execute();
return($dbh->{'mysql_insertid'});

Perlでユニークなidを引っ張るならこんな感じ

この方式であれば、Shardingした系統がいくつになっても常にユニークなidが採番できるため、アプリの作りもデータの持ち方もシン プルにすることができます。なお、MyISAMのテーブルロックがあるため同時並列性はないですが、現状のモバゲータウンのトラフィックであってもこの方 式で特に問題は発生していません。ある程度の規模が見込まれて、Shardingすることが予想されるのであれば、こういった方式がおすすめです。

ログ系テーブルのまとめ

というわけでまとめ

  • なるべく最新のデータへのアクセスだけで済む様に設計する
    • 全データを永遠にDBに残すとかの仕様にしない
  • MySQL 5.1以上オヌヌメ
  • RANGEパーティション使って、古いのを簡単に消せる様にする
    • 刈り込みも使える時は有効活用
  • idの払い出しは専用の採番テーブルを使うか、5.1以上のauto_increment
    • Shardingする場合には、採番テーブルが便利

実は、今回紹介した以外の方法でログ系のテーブルを一定程度のサイズに押さえこむテクニックもあるのですが、それはこの記事が人気が出たら書くことにしましょう^^

おわりに

今回は以上です。列挙しといて1種類しか話しなくてすみませんすみません。また、言葉ばっかりで分かり辛く、ベンチとか正確に取ってるわけじゃないのでいい加減な話ですが、「モバゲータウンではこうやってるよ」的な話として小耳に挟んで頂ければ幸いです。

buffer poolとかInnoDBのデータ構造とか、そういう話も今回は省略してしまいました。その辺の仕組みは小難しい本を読むのが一番なのですが、忙しいあなたのために3分クッキングしたいなとかも思ってたりします。

で、なんで僕がこんなことやってるかと言うと、丁度約1年前には僕はselect文すら満足に書けない人だったんですが、1年もやればこんだけ色々できるようになるんだよー、インフラって別に怖くないよー、みんなインフラも興味持とうよー、ということが伝えたいのであります。

ではでは。

God bless your MySQL!

引用元

更新:2010/11/16 11:37 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.2834 MySQLウォッチ---目次

MySQLウォッチ---目次

 

引用元

更新:2010/07/19 21:02 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.2720【引用】別サーバのmysqlに接続し、SQL文ファイルを実行する

別サーバのmysqlに接続し、SQL文ファイルを実行する

別サーバのmysqlに接続する

mysql -h ホスト名 -uユーザー名 -p DB名

別サーバのmysqlに接続し、SQL文ファイルを実行する

mysql -h ホスト名 -uユーザー名 -p DB名 < テキストファイル名

とすると、パスワード入力が促され、入力後に実行されます。

引用元

更新:2010/06/23 00:29 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.2308【引用】MySQLコミュニティ騒然!MySQL5.5.4が与えるインパクト。

MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。

先週、MySQL Conference & Expo 2010が開催され、盛況のうちに終了した。カンファレンスに合わせる形で、MySQL 5.5.3および5.5.4がリリースされたのだが、これが目を見張るような進化を遂げている。特に性能面での進化には目を見張るものがあ る!Jeremy ZawodnyやMark Calleghanといったコミュニティの重鎮たちも 「非常にエキサイティングなリリースだ!」などと表して歓迎の意を表している 。

というわけで、本日はMySQL 5.5.3/5.5.4の新機能および変更点...

引用元

更新:2010/04/21 00:47 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.2220 [勝手に補足]これだけは覚えておきたい!!MySQLの6つの自動変換

[勝手に補足]これだけは覚えておきたい!!MySQL の6つの自動変換
sakaikさんの首記の記事はうまくまとまっていますが、ところどころ勘違いする人や、じゃどう対処すればいいの?と思う人がいるのではないかなー、と思いましたので勝手に補足しておきます。(単なる便乗エントリともいいますがw)

1. [数値] 範囲外の数値は頭を押さえつけられる
2.[数値] 小数は勝手に丸められる(warningも出ない)
3.[数値] 数字以外が入っていると分かるところまでを登録してくれる
4.[文字列] 長さを超えると勝手にカットされる
5.[文字列] 「文字列連結」のつもりでうっかり + を使うな!!
6.[日付] 年を2ケタで指定する際の変換ルール

既存の環境を引き継ぐ時には、上記の気配りはエラーを減らしてくれるけれども、新規の開発には混乱を招くこともあるので、男はだまって次の設定をしましょう。

mysql> SET [GLOBAL|SESSION] sql_mode='TRADITIONAL'

いまの接続に適用したい場合は、SET SESSION~。
今後の接続に適用したい場合は、SET GLOBAL~で~。

それにより、以下のものについてはエラーがでるようになります。

1. 3.4.

1.設定前
mysql> INSERT INTO numtest VALUES (2200000000);
Query OK, 1 row affected, 1 warning (0.02 sec)

1.設定後。エラーがでてデータは入らない。
mysql> SET SESSION sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO numtest VALUES (2200000000);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

3.設定後。
mysql> INSERT INTO numtest VALUES ('12345a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1

4.設定後
mysql> CREATE TABLE strtest (a VARCHAR(10)) ENGINE=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO strtest VALUES ("123456789ABCDE");
ERROR 1406 (22001): Data too long for column 'a' at row 1

SQLモードについては、このマニュアルをご参照ください。

では2.はどうでしょうか? これは動作を変更することはできないはずです。MySQLに限らずほとんどのデータベースでは数値同士のキャストを暗黙的に行い代入先の範囲を超えない場合には、Warningも出しません。

5.は独特なものですが、元々+を文字列連結演算子に使うのは
Sybase/Microsoft SQL Serverくらい
です。

ちなみに文字列連結はSQL標準では||ですが、MySQLでは||がORにマップされているため通常はCONCAT()関数を利用します。

||をORにマップせず、文字列連結に使うにはPIPES_AS_CONCATをsql_modeに指定します。

ちなみにOracleにもCONCAT()関数は存在しますが、引数は2個までです。
# MySQLは可変引数で設定できます。

6.については、まぁ各DB様々ですのでMySQLに限った話ではありません。素直に省略せずに書きましょう。

さて、最後に「6つの」というのは座りが悪いので、七個目をこれまた勝手に追加しておきましょう。

7.[文字列] 知らない漢字以降は勝手にカットされる
例えばMySQL 5.1/5.5のUTF8(3-byteまで)に対して、サロゲートペア(4-byte)の文字を入れようとすると、その文字だけでなく、それ以降の文字もカットされます。

mysql> CREATE TABLE t1 (c1 VARCHAR(30)) CHARSET utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> -- "abc" + 4byte UTF-8(0xF0909080) + "def"
mysql> INSERT INTO t1 VALUES(0x616263F0909080646566);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> -- "def" is truncated
mysql> SELECT C1 FROM t1;
+------+
| C1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)

このように中途半端にデータが入ることを防ぐためには、冒頭で述べたようにSQL_MODEを設定します。

mysql> SET SESSION SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(0x616263F0909080646566);
ERROR 1366 (HY000): Incorrect string value: '¥xF0¥x90¥x90¥x80de...' for column 'c1' at row 1

現状、これを防ぐためには文字列をバイナリ列として格納する(MySQL5.1/5.5)もしくは、MySQL6.0のサロゲートペア対応のキャラクタセットを利用するしかありません。詳細については、以下の松信さんの資料をご参照ください。

MySQL日本語問題洗い出し (2008.6 OSC.DB)

それでは、また!

[2010-03-04 追記]
本家のほうに、補足記事がでました。いろいろと先走って補足してすまねえっす。

[mysql]MySQLの自動変換を丁重にお断りするためのたった1種類の呪文

引用元

更新:2010/03/21 17:37 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ

No.2131 これだけは覚えておきたい!!MySQLの6つの自動変換

これだけは覚えておきたい!!MySQL の6つの自動変換

 MySQLはとても気ぃつかい屋さんである。我々が投げる多少あいまいな指示も頑張って解釈し、なんとか文句を言わずに実行してみようと挑戦してみてくれる。

 今日はそんなMySQLがケナゲに解釈してくれる自動変換について紹介しようと思う。この自動変換、ケナゲなMySQLの奥ゆかしさ故、出した指示と異なる動作をされたことに気がつかないことがある。ここで紹介する6つの自動変換をしっかり脳ミソにたたき込んでおけば、無用なトラブルにハマる時間も減るかもしれない。


1.[数値] 範囲外の数値は頭を押さえつけられる

 intやsmallint、bigintなどの数値型には、扱える範囲が決まっている。例えばint型なら最大21億ちょっとだ(unsignedの場合は43億弱)。これより大きい数字を登録するよう指示を出すとMySQLはどうするか。そう、頑張って入れられるところまで入れてくれるのである。「入れられるところまで」といっても制限を超えられるわけではない。ちゃんとした値を入れないで仕事を終えたことにしてしまうのだ。遠慮がちに「1264:Out of range value for column 'col1' at row 1」とつぶやきながら。


 具体例を挙げよう。

mysql> CREATE TABLE numtest (a INT) ENGINE=InnoDB;
mysql> INSERT INTO numtest VALUES (1);
Query OK, 1 row affected (0.06 sec)

 これは正常なケースの実行例だ。1行を登録したと返事が返ってきている。次に INT 型の範囲を超える値を登録依頼してみる。


mysql> INSERT INTO numtest VALUES (2200000000);
Query OK, 1 row affected, 1 warning (0.05 sec)

 OKと返ってきた。たださっきと違うのはその後に「1 warning」とあることだ。これを見落とすと痛いことになる。しっかりMySQLサーバからの返事はチェックするようにしたい。

 ここで返ってくるワーニングの内容はこんな感じ。範囲外だよ、と言っている。

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+

 範囲外ではあるが登録自体はされていることに注意したい。

mysql> SELECT * FROM numtest;
+------------+
| a          |
+------------+
|          1 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

 そう。登録をお願いした2200000000とは違う値として。

お願いしたものと違う値をとりあえず登録して処理をエラーで停めない気配り。それがMySQLである。



2.[数値] 小数勝手丸められる(warningも出ない)

 次は小数だ。INTのような整数カラム小数値を登録するよう依頼するとどうなるか。なんとMySQLは「整数カラムなんだから小数が来るわけない」と気を利かせて、一番近い整数の値として登録してしまうのだ。つまり四捨五入だ。しかも奥ゆかしさ故か一切の文句を言わない(ワーニングを出さない)


 以下が実行例だ。

mysql> INSERT INTO numtest VALUES (0.1);
mysql> INSERT INTO numtest VALUES (12.345);
mysql> INSERT INTO numtest VALUES (24.5);

mysql> SELECT * FROM numtest;
+------+
| a    |
+------+
|    0 | 
|   12 | 
|   25 | 
+------+

 これは値を与える時に開発者が気をつけなければならない。登録をお願いしている立場からすれば「無理しないで言ってくれればいいのに・・・・」であるのだが。。。



3.[数値] 数字以外が入っていると分かるところまでを登録してくれる

 数値型のカラムへ、"123AB" や "123AB456" のように数字以外のものが入っている値を登録依頼すると、MySQLは前から見ていって数値としてわかるところまでを登録してくれる。この例では「123」まで見たところで「A」という数字以外の文字に突き当たるので "123" という数字とみなして登録してくれるわけだ。ただし一応ぶつぶつ文句は言ってくる。

mysql> INSERT INTO numtest VALUES ("12B345");
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+

 値はしっかりと、わかったところまでを登録してくれている。

mysql> SELECT * FROM numtest;
+-------------+
| a           |
+-------------+
|         123 |
+-------------+

 また、先頭から見て1文字も数字を見ないうちに数字以外の文字にあたってしまった場合、さすがのMySQLもギブアップする。・・・かとと思いきやこの場合も頑張ってしまうのである。いやはやケナゲ。もちろんいくら頑張ったところで頑張った結果の値は「ゼロ」である。

mysql> INSERT INTO numtest VALUES ("AB123");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'AB123' for column 'a' at row 1 | 
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM numtest;
+------+
| a    |
+------+
|    0 | 
+------+

 ここまでで紹介した数値に関する3つのワーニング。エラーコードがそれぞれ違ことに気づいただろうか。優秀なエンジニアたるもの、常にエラーコードには目を光らせておきたい。




4.[文字列] 長さを超えると勝手カットされる

 次は文字列だ。カラム長を越えた文字列の登録を依頼すると入るところまでを登録し、あとは捨ててしまう。一応ブツブツ言うので聞き漏らさないようにしたい。

mysql> CREATE TABLE strtest (a VARCHAR(10)) ENGINE=innodb;
mysql> INSERT INTO strtest VALUES ("123456789ABCDE");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

 truncateして(切り捨てて)登録したと言っている。数値型への登録に文字を与えたときと同じエラーコードであることに気づいただろうか。MySQLが自らの判断で与えられた値の一部を捨ててしまった時にはこのコードのワーニングが出る。

 入るところまでは精一杯登録されていることが以下より確認できる。

mysql> SELECT * FROM strtest;
+------------+
| a          |
+------------+
| 123456789A | 
+------------+


 なおCREATE TABLE時に VARCHAR(長さ)で定義した長さは「文字数」なので改めてここで確認しておきたい。いまや、昔から言われているような「日本語1文字は英語2文字ぶん」などとは別の常識を持つ世界なので気をつけておこう。

mysql> INSERT INTO strtest VALUES ("あいうえおかきくけこさし");
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SELECT * FROM strtest;
+--------------------------------+
| a                              |
+--------------------------------+
| あいうえおかきくけこ           |
+--------------------------------+

 このように VARCHAR(10) に対しては10文字(5文字ではない!!)までに切りつめられた文字列が登録されることがわかる。



5.[文字列] 「文字列連結」のつもりでうっかり + を使うな!!

 これは数値型と文字型の自動変換の合わせ技だ。

 MySQLでは「+」記号は、数値の和算として働く。文字列を連結するために使うのは CONCAT() 関数だ。

  1. を使うとどうなるか、実例で見てみよう。
mysql> INSERT INTO strtest VALUES (123);
mysql> INSERT INTO strtest VALUES (123+27);
mysql> INSERT INTO strtest VALUES ("123"+27);
mysql> INSERT INTO strtest VALUES ("123"+"27");

 これらはすべて、文字列型のカラムへの挿入処理だが、すべて数値として処理されている点に注目だ。特に4番目の例は文字列どうしを連結している(とおそらくこのクエリの書き手は考えている)のに、実際には一旦数値化して計算されていることに、MySQLの気配りが感じられる。


 処理結果:

mysql> SELECT * FROM strtest;
+--------------------------------+
| a                              |
+--------------------------------+
| 123                            |
| 150                            |
| 150                            |
| 150                            |
+--------------------------------+


 ここまで見てきた方なら、以下のSQL文によって実施に何が登録されるのか、すぐにおわかりだろう。

mysql> INSERT INTO strtest VALUES ("image"+"37"+".png");

 これは正しくは以下のように書くべきなのである。

mysql> INSERT INTO strtest VALUES (CONCAT("image","37",".png"));


6.[日付] 年を2ケタで指定する際の変換ルール

 MySQL の日付(または日付時刻)型は懐が広く、様々なフォーマットで与えられた「日付らしきもの」を理解しようと最大限の努力をしてくれる。日付時刻のうち日付だけに焦点を当てて見ると、YYYY-MM-DD や YYYY/MM/DD, YYYYMMDD のような表記の揺れを受け付けてくれるばかりか、実は「年は2桁でも良い」というルールもある。 YYYYMMDD の代わりに YYMMDD と与えても良い、というわけだ。

 このMySQLの気づかいがどういうことを引き起こすか見てみよう。もうワーニングについてはいちいち紹介しない。ワーニングが出るものも出ないものもあるので自身で試してもらいたい。

CREATE TABLE dttest (a DATETIME) engine=innodb;

INSERT INTO dttest VALUES (null);
INSERT INTO dttest VALUES ("");
INSERT INTO dttest VALUES ("2010");
INSERT INTO dttest VALUES ("201001");
INSERT INTO dttest VALUES ("20100123");

 nullを登録すればnullのまま登録されるのは良いとして、ブランクや2010のように日付型の要件を満たしていない場合は、なんと 0000-00-00 として登録されてしまう。日付型の変数値として 0000-00-00 を認めていない言語ではMySQLからデータを取得した途端にエラー(または例外発生)となるのでこの値は厄介だ。

 最後の 20100123 は、2010年1月23日として登録されるのは予想通りだろう。曲者なのが "201001" だ。なんとなく「2010年01月」のつもりだったようにも見える。だが年月日は3点セットであり、年月だけを与えるということはあり得ないのだ。ここで「年は2桁でも良い」ルールが発動する。 なんとMySQLはこの数字を YYMMDD 形式で与えられたものと解釈して、「2020年10月01日」として登録するのだ。これはびっくり。


 ところで日付型についてマニュアルにはこうある:

サポートされている範囲は '1000-01-01' から '9999-12-31' です。」

  とはいえ、実際には西暦1000年以前も登録できてしまうので、いったい何を信用して良いのやら悩んでしまうところではある。

mysql> INSERT INTO dttest VALUES ("645-02-25");
mysql> SELECT * FROM dttest;
+---------------------+
| a                   |
+---------------------+
| 0645-02-25 00:00:00 |
+---------------------+

 話を戻して、年を2桁で与えた時の自動変換だが、これは「69年までは2000年代、70年からは1900年代」として解釈される。

mysql> INSERT INTO dttest VALUES ("69-02-25");
mysql> INSERT INTO dttest VALUES ("70-02-25");
mysql> SELECT * FROM dttest;
+---------------------+
| a                   |
+---------------------+
| 2069-02-25 00:00:00 |
| 1970-02-25 00:00:00 |
+---------------------+

 年を2桁ではなく必ず4桁で与える習慣が最善だが、そうでない場合、うっかりするとエラーにならず思っていたのと違う日付が登録されてしまうので気をつけたい。



 以上、MySQLでハマりやすい自動変換について紹介た。MySQLの気配りと上手に付き合い、みなさんのMySQLライフ一助となれば幸いである。

 なお、「SQLモード」を指定するとこれらの動作を変更することができる。SQLモードについては気が向いたらいつか紹介してみたい。



引用元

更新:2010/02/26 10:27 カテゴリ: MySQL  > 知識.ユーティリティ ▲トップ
42件中 1 〜 10 表示  1 | 2 | 3 | 4 ... 次の10件> 最後»

FuelPHP

Mac

フロントエンド開発

web開発

プロマネ

マネタイズ

プレゼン

webサービス運用

webサービス

Linux

サーバ管理

MySQL

ソース・開発

svn・git

PHP

HTML・CSS

JavaScript

ツール, ライブラリ

ビジネス

テンプレート

負荷・チューニング

Windows

メール

メール・手紙文例

CodeIgniter

オブジェクト指向

UI・フロントエンド

cloud

マークアップ・テキスト

Flash

デザイン

DBその他

Ruby

PostgreSQL

ユーティリティ・ソフト

Firefox

ハードウェア

Google

symfony

OpenPNE全般

OpenPNE2

Hack(賢コツ)

OpenPNE3

リンク

個人開発

その他

未確認

KVS

ubuntu

Android

負荷試験

オープンソース

社会

便利ツール

マネー

Twig

食品宅配

WEB設計

オーディオ

一般常識

アプリ開発

Python

サイトマップ

うずら技術ブログ

たませんSNS

rss2.0