のんびり、のほほん。: SQL ServerからMySQLのデータベースにアクセスしてみる。

Surface2が十分な速さです。

仕事場で使ってるんですけどね。
個人的にはクライアント機はもうSurface2でいいかな、という感じがします。
けどまぁ、普段でもプログラムを作ったりするんで。
やっぱりプログラムできる端末からは離れられないんですけどね。

SQL ServerからMySQL。

仕事でMySQLのデータをSQL Serverに移すことを考えました。
最終出来にはSQL Azureに持っていきたいんですけどね。
その通過点としてSQL Serverを通してみよう、と。

MySQL → SQL Server → SQL Azure

ってなことを考えたんですが…。
SQL Azure Data Sync、自動更新間隔が5分は長い。
それなら、移行のためだけにしか使えない…。
というわけで、とりあえずはデータの移行です。

SQL Serverのリンクサーバー機能。

SQL Serverには、リンクサーバー機能というのがあって、他のデータベースを直接弄る機能があるみたいです。
うまく設定すれば、Oracleも見に行けるようです。
まあ、最終的にはODBC通すのでそのドライバさえあればPostgreSQLだろうがSQLite（笑）だろうがどんなデータベースでも見れるんでしょう。
たぶん。

ODBC経由でMySQLに接続できるようにする。

まずは、MySQLの設定はUTF8にしておく。
日本語使いたいなら文句があろうともそうしておくべきでしょう。
で、MySQLのODBC Unicodeドライバで接続名を「MySQL」で接続できるようにしておきます。
この辺は適当なサイトを参考にしてください。
あまり深く考えなくても素直に設定すれば大丈夫です。

SQL Server Management Studioで設定をする。

リンクサーバー機能の設定は、通常ならManagement Studioの「オブジェクトエクスプローラー」内の「リンクサーバー」の部分で「新しいリンクサーバー」で行います。

ここではSQLコマンドを入力して設定します。
設定は、以下のようなコマンドで行います。

exec master.dbo.sp_addlinkedserver
@server=N'MySQL',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@datasrc=N'MySQL',
@provstr=N'DRIVER={MySQL ODBC 5.2 UNICODE Driver};SERVER=127.0.0.1;PORT=3306;DATABASE=mysql_test_data;USER=loginuser;PASSWORD=loginpass;OPTION=3;'

exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MySQL',
@locallogin=NULL,
@rmtuser=N'loginuser',
@rmtpassword=N'loginpass'

まあ、とりあえず見れば設定の変更方法はわかってもらえると思います。
接続情報を変えてもらえば大丈夫なんじゃないでしょうか。
理屈としては、SQL Server → MSDASQL → ODBC → MySQL と繋がってる感じです。

これを実行すると、上記の「リンクサーバー」の部分に新しくMySQLへの接続ができます。

これで、接続の設定ができました。
クエリは以下のように発行します。

select * from openquery (MySQL, ‘select * from test’)
go

MySQLという識別子のサーバーに文字列のクエリを投げ、その戻り値をさも自分が行ったかのように返却する…、ということになります。
なので、MySQLに渡すクエリ文字列さえ作れれば、どんな戻り値も受け取れます。

テーブルのコピーを作る。

テーブルのコピーは以下のようにすると簡単に作れます。

select * into [my_table].[test] from openquery (MySQL, ‘select * from ex_table.test’)
go

ただし、これで作ってしまうとprimary keyやreferencesなどの設定項目は引き継がれません。
あくまでもテーブルのフィールド形式をコピーする感じです。
なので、MySQL側にlimit 1をつけてテーブルを作り、あとからManagement Studioでcreateをスクリプト化してprimary keyやreferencesを付け加えたりします。

注意しなければいけないこと。

注意点は、それぞれのデータベースの特質の違いにあります。
たとえば、SQL Serverに比べてMySQLは、

enum型がある
時間の範囲が広い
null禁止でもnullが入る時がある
zerofillがある

とか。
最後のはまあほぼどうでもいいのですが。

enum型の問題。

MySQLには、入るデータを最初に定義するenum型があります。
SQL Serverのcheckが該当すると思うのですが。
このデータはODBCを通すと文字列に見えますが、取得できるデータサイズと返されるデータとの長さが違うため、以下のようなエラーが発生します。

サーバー : メッセージ 7347、レベル 16、状態 1、行 1 
　OLE DB プロバイダ 'MSDASQL' は固定長列 '～' に予期しない長さのデータを返しました。 
　期待するデータ長は x ですが、返されたデータ長は x です。

最初、何が起こったのかよくわからなかったんですが。
これの回避方法は、文字列に型変換してしまうことです。

select * into [my_table].[test] from openquery (MySQL, ‘select iid, cast(data_type as char) as data_type from ex_table.test’)
go

問題なのは、いちいちカラムを全部書かなければいけなくなってしまうこと。
面倒くさいです。
なので、UnixでMySQLにつなげられる環境なら、こんなコマンドラインで

mysql -u xxx -pxxx -q -s -e 'show columns from ex_table.test' | awk '{print $1 ","}'

カラム名の一覧を表示させ、コピペしてしまいます。

時間の問題。

MySQLのDateTimeのほうがSQL ServerのDateTime型より範囲が広いようです。
それでかどうかわかりませんが、たまにDateTimeフィールドがnullで入ってくるときがあったりするんですよね。
上記のテーブル作成を行うと、MySQLのDateTimeがDateTime2として作成されるのでカバーできるはずなんですが、やっぱりうまくいかなかったりする時があります。
しかも、null禁止でもnullが入る問題もあったりして、困ったもんです。

insert into [my_table].[test] select isnull(open_dt, ‘1999-01-01 00:00:00’) from openquery(MySQL, ‘select ifnull(open_dt, ‘1999-01-01 00:00:00’ as datetime) from ex_table.test’)

とか、もう何が何だか。
時間関連の問題は難しいです。
変換に失敗したりして、もう、どうしたらええんやー、と頭を抱えてしまいます。

艦コレをやりたいわけではなく。

流行ってますねー。
艦コレ。
とりあえずまだやってないんですが、しばらくやらないんじゃないかなー。

でもSurface2は欲しいです。

のんびり、のほほん。

2013年12月6日

SQL ServerからMySQLのデータベースにアクセスしてみる。