memo

  • 大文字、小文字混じりのユニークなものは型をBINARYにしないとだめ。
  • delete文で、サブクエリを使いin区を使うと、糞遅くなるので、100件づつとかに小分けにした方がよい。

mysqlだけでYYYYmmdd_Hisのファイルをアウトプットする。

/*concatを使って変数@sqlへselect文を入れる*/
set @sql=CONCAT("SELECT no , onair , donari INTO OUTFILE '",DATE_FORMAT(now(),'%Y%m%d_%H%i%S.txt') ,
"' FROM base a LEFT JOIN mp3_list b USING(onair) WHERE a.flag IS NULL AND a.onair >= CURDATE();");
/*ここからが本題*/
PREPARE sql1 FROM @sql;
EXECUTE sql1;
DEALLOCATE PREPARE sql1;

bitフラグ判定

EXPORT_SETを使う。
引数はEXPORT_SET(カラム名,'ONの時の文字','OFFの時の文字','区切り文字',桁数)になる。

SELECT EXTPORT_SET(bit_flag,'1','0','',32) test1 FROM users;
#bit_flagが123なら
+--------------------------------+
| test1                          |
+--------------------------------+
|11011110000000000000000000000000|
+--------------------------------+

Tips

ストアドプロシージャ

mysql上のfunctionとかdefみたいなもん。
引数を取れるとは知らんかったorz MySQL ストアドプロシージャ

MyISAMを圧縮

過去ログの場合に使う(シェルから使うので注意)

#xamppを使っている場合はroot権限じゃないとダメ
myisampack foge/foo/path/table名
#拡張子は指定しなくてもOK
- Calculating statistisc
カラム数
- Compressing file
カラム数


まとめ

限界までMySQLを使い尽くす!!より

JOINできるテーブル数

61

サブクエリのネスト数

31回まで。(最外部クエリを合わせると全体で32階層になる。)

テーブルサイズ

MySQL自体には制限はない。 InnoDBの場合は、テーブルスペースの最大サイズである64TBが実質の上限となる。それ以前に、64TBのファイルを作成できる必要があるのだが、ext3などのファイルシステムでは、ファイルサイズの上限が2TBなので、それより大きなテーブルを作成したい場合には、テーブルスペースを複数のファイルで構成する必要がある。
MyISAMの場合も、.MYIまたは.MYDファイルがOSまたはファイルシステムがサポートしているファイルの最大サイズの制限を受ける。
Linuxの場合、ext2/ext3では2TB、ext4では16TB、XFSでは8エクサバイトまでである。
SolarisではUFSの場合1TB、ZFSの場合16エクサバイトである。
ただし、MyISAMはファイルサイズの他に、内部で利用しているポインタサイズによってもテーブルサイズの制限を受ける。デフォルトでは256TBまで(ただし5.0.6以前は4GBまで)のテーブルが作成可能である。
ポインタサイズはCREATE TABLE実行時にMAX_ROWSおよびAVG_ROW_LENGTHオプションで指定可能である。(MAX_ROWS x AVG_ROW_LENGTHが希望するテーブルサイズ程度になるようにする。)
MySQL Clusterの場合はDataMemoryやIndexMemoryのサイズ、およびディスク型テーブルの場合はテーブルスペースのサイズが上限となる。

テーブルあたりの行数

MySQL自体にはテーブルごとの行数の制限はない。
ただし、PRIMARYやUNIQUEインデックスが格納できる行数には限界があるので、だいたいはそちらの制限を受けることになる。
例えばPRIMARY KEYのデータ型がINTの場合は-2147483648〜2147483647、INT UNSIGNEDの場合は0〜4294967295までという値の範囲をとるので、4294967295行以上を格納することは出来ないわけである。

テーブルごとのカラム数

MySQLの制限値は4096個までである。さらに、ストレージエンジンごとにそれより小さい制限があればそれに従う。
InnoDBの場合は1000カラムまでで、MyISAMは特に制限はなく4096個というMySQL自体の制限が適用される。
MySQL Clusterではカラムとインデックスを合わせて128個までという制限がある。

テーブルごとのインデックス数

ストレージエンジンには最低16個以上のインデックスをサポートすることが定められているが、上限は定義されていない。
従って、ストレージエンジンごとの制限がそのまま適用される。
MyISAMは64個、InnoDBは上限なし、MySQL Clusterはカラムとインデックスを合わせて128個までである。

テンポラリテーブルサイズ

テンポラリテーブルは、メモリベースのものとディスクベースのものが存在する。
メモリベースのもののサイズが限界に達すると、MyISAMに変換される。
変換の処理が結構重いので、変換があまり発生しないようにテンポラリテーブルのサイズやクエリをチューニングする必要がある。
メモリベースのテンポラリテーブルのサイズは、tmp_table_sizeおよびmax_heap_table_sizeオプションで設定することができる。
ディスクベースのテンポラリテーブルは、tmpdirの領域を使い尽くすまで作成可能である。

テーブルあたりの行数

MySQL自体にはテーブルごとの行数の制限はない。
ただし、PRIMARYやUNIQUEインデックスが格納できる行数には限界があるので、だいたいはそちらの制限を受けることになる。
例えばPRIMARY KEYのデータ型がINTの場合は-2147483648〜2147483647、INT UNSIGNEDの場合は0〜4294967295までという値の範囲をとるので、4294967295行以上を格納することは出来ないわけである。

SQL文の最大長

MySQLサーバーが実行出来るSQL文の最大長は、max_allowed_packetシステム変数で表される。(max_allowed_packetの最大値は1GBである。)
max_allowed_packetの値はセッションごとにも設定可能なので、デフォルトではそこそこの値(16MBなど)に設定しておいて、必要に応じて大きな対を使うと良いだろう。

DATETIMEとTIMESTAMPの最大値

DATETIMEは1000-01-01 00:00:00〜9999-12-31 23:59:59、1970-01-01 00:00:01〜2038-01-09 03:14:07(ただし時間帯はUTC)まで格納することが出来る。
TIMESTAMPは2038年問題にぶち当たるわけである。