Prepared statementはステートメントを一度定義して、それを
何回も違う引数で実行するものです。これはセキュリティを増し
なおかつ効率のよい方法で、アドホックなクエリのストリングに
置き換わるものです。典型的なprepared statementは以下のようです。

SELECT * FROM Country WHERE code = ?

”?”はいわゆる場所とりです。上のクエリを実行するときはこの場所
に値がいります。ではどうして、prepared statementを使うのでしょう。

アプリケーションでprepared statementを使用することで、
セキュリティや性能の理由で幾つもの利点をもたらします。

Prepared statementはSQL のロジックとデータを分離することで
セキュリティを増加します。ロジックとデータを分離することで、SQL
インジェクション攻撃を回避することができます。通常のクエリを
扱っている場合、ユーザから受け取ったデータを処理するには
注意が必要です。これはシングル・クオート、ダブル・クオート、
バックスラッシュなどの文字をエスケープする関数を使用すること
に関係します。こういったことはprepared
statementを使用する際には不必要です。
データを分離することでMySQLは自動的にこういった文字を考慮して
おり特別な関数を使用してこういう文字をエスケープする必要が
ありません。

prepared statementでの性能向上はいくつかの異なった機能によります。
まず最初に、クエリを一度しかパースしなくてよいことです。
最初にステートメントの用意をした際、MySQLはステートメント
をパースしてシンタクスをチックして、クエリの実行の用意を
します。同じクエリを何回も実行するのであれば、そのオーバヘッド
は2度目からありません。あらかじめ、パースしてあることで例えば
なんどもINSERTステートメントを使うような場合、スピードが増加します。

2つ目の性能向上は新しいバイナリーのプロトコルによります。
今までのプロトコルはネットワークを介して転送する前に、
全てをストリングに変換していました。クライアントはデータを
ストリングに変換し(大抵の場合もとのデータより大きい)、ネットワーク
(か他の方法で)サーバに転送します。サーバはストリングをもとの
正しいデータタイプに変換します。バイナリー・プロトコルであれば
このオーバーヘッドがありません。全てのタイプはそのままの
形で(バイナリー形式)で転送されます。そのためCPUの使用も
削減され、ネットワークの使用も押さえることができます。


mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select ID,NAME from City where ID > 1000 and ID < 1010; +------+---------------+ | ID | NAME | +------+---------------+ | 1001 | Depok | | 1002 | Citeureup | | 1003 | Pemalang | | 1004 | Klaten | | 1005 | Salatiga | | 1006 | Cibinong | | 1007 | Palangka Raya | | 1008 | Mojokerto | | 1009 | Purwakarta | +------+---------------+ 9 rows in set (0.00 sec) mysql> PREPARE p1 FROM "SELECT ID,Name FROM City WHERE ID > ? and ID < ?"; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @atai1 = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @atai2 = 1010;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE p1 USING @atai1,@atai2;
+------+---------------+
| ID | Name |
+------+---------------+
| 1001 | Depok |
| 1002 | Citeureup |
| 1003 | Pemalang |
| 1004 | Klaten |
| 1005 | Salatiga |
| 1006 | Cibinong |
| 1007 | Palangka Raya |
| 1008 | Mojokerto |
| 1009 | Purwakarta |
+------+---------------+
9 rows in set (0.00 sec)

mysql>

mysql_prepared_statement

その他の例

prepare_1

補足
SQLステートメントの削除

DEALLOCATEステートメントは,登録したSQLステートメントを削除する。
以下のSQLステートメント「p1」を削除している。

mysql> DEALLOCATE PREPARE p1;
Query OK,0 rows affected (0.00 sec)

※ DROP PREPAREでもOKですし、セッションを切ればPREPAREもなくなります。

以下の例では、?を使わずにPREPAREを作成して最後に削除しています。

mysql> PREPARE P_Time from 'select now()';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute P_Time;
+———————+
| now() |
+———————+
| 2009-11-12 00:29:43 |
+———————+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE P_Time;
Query OK, 0 rows affected (0.00 sec)

mysql>

prepare_time

このようにプリペアド・ステートメントは,SQLステートメントを登録して実行する
機能を提供する。プリペアド・ステートメントは,事前にSQLステートメントを
登録しておくので,パラメータのみを受け渡すだけでデータベース処理が可能である。

参考サイト
[MySQLウォッチ]第11回 リリース迫る4.1


IF(expr1,expr2,expr3)

expr1 が TRUE(expr1 <> 0 および expr1 <> NULL)の場合 IF() は expr2 を返し、
それ以外の場合は expr3 を返す。 IF() は、使用されているコンテキストに応じて、数値または文字列を返す。

IFNULL(expr1,expr2)

expr1 が NULL でない場合は expr1 を返し、それ以外の場合は expr2 を返す。IFNULL() は、
使用されているコンテキストに応じて、数値または文字列を返す。
※これは、MS SQLでいうISNULLにあたる。


mysql> SELECT IFNULL(@a, '@a is NULL');
+--------------------------+
| IFNULL(@a, '@a is NULL') |
+--------------------------+
| @a is NULL |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(@a,@a,'@a is null');
+------------------------+
| IF(@a,@a,'@a is null') |
+------------------------+
| @a is null |
+------------------------+
1 row in set (0.00 sec)

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@a,@a,'@a is null');
+------------------------+
| IF(@a,@a,'@a is null') |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(@a,@a,'@a is null');
+------------------------+
| IF(@a,@a,'@a is null') |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(STRCMP('test','test'),'no','yes');
+--------------------------------------+
| IF(STRCMP('test','test'),'no','yes') |
+--------------------------------------+
| yes |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>

variable_ifnull1

参考サイト
6.3.1.4. フロー制御関数


You can store a value in a user-defined variable and then refer to it later.
This enables you to pass values from one statement to another. User-defined variables
are connection-specific. That is, a user variable defined by one client cannot be seen
or used by other clients. All variables for a given client connection are automatically
freed when that client exits.

SET @var_name = expr [, @var_name = expr] …

For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case,
the assignment operator must be := and not =

    because = is treated as a comparison operator

in non-SET statements:


mysql> select @a1;
+-------+
| @a1 |
+-------+
| test1 |
+-------+
1 row in set (0.00 sec)

mysql> SET @a2 := 'test2';
Query OK, 0 rows affected (0.00 sec)

mysql> select @a2;
+-------+
| @a2 |
+-------+
| test2 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT @a3 := 'test3';
+----------------+
| @a3 := 'test3' |
+----------------+
| test3 |
+----------------+
1 row in set (0.00 sec)

mysql> select @a3;
+-------+
| @a3 |
+-------+
| test3 |
+-------+
1 row in set (0.00 sec)

mysql>

variables

User variables can be assigned a value from a limited set of data types:
integer, decimal, floating-point, binary or nonbinary string, or NULL value.

mysql> SET @t1=1, @t2=2, @t3:=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
| 1 | 2 | 4 | 7 |
+------+------+------+--------------------+
1 row in set (0.00 sec)

mysql>

variables2


mysql> SET @t1='test', @t2='-OK';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @t1, @t2:= concat(@t1,@t2);
+------+-----------------------+
| @t1 | @t2:= concat(@t1,@t2) |
+------+-----------------------+
| test | test-OK |
+------+-----------------------+
1 row in set (0.00 sec)

mysql>

variables3

※ ユーザーVariableは、大文字、小文字の区別はしないので以下のような結果になる。
variable_not_case_sensitive

参考サイト
8.4. User-Defined Variables
MySQL独自のENUM・SET型を使ってみよう


LOAD DATA INFILE は SELECT … INTO OUTFILE の補数です。

    テーブルからファイルにデータを書き込むには、SELECT … INTO OUTFILE を利用してください。

テーブルにファイルをリード バックするには、LOAD DATA INFILE を利用してください。
両方のステートメントに対して FIELDS と LINES 条項の構文は同じです。
条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。


select PID,NAME
INTO OUTFILE 'project.dat'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM project
ORDER BY id
LIMIT 5;

into_out_file


select *
INTO OUTFILE 'project.dat'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM project
ORDER BY id
LIMIT 5;


select *
INTO OUTFILE 'project_no_option.dat'
FROM project;

into_out_file_no_option

参考サイト
12.2.5. LOAD DATA INFILE 構文

12.2.7. SELECT 構文


MYSQLDUMPによるデータベースのバックアップ
何も指定しない場合は、以下のようになっている。

[root@colinux tmp]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --default-character-set=utf8 --quick --max_allowed_packet=16M --default-character-set=utf8

[root@colinux tmp]#

mysql_dump_review4

特定のデータベースから、全てのテーブルのデータのみをバックアップ
[root@colinux tmp]# mysqldump --no-create-info --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review

[root@colinux tmp]# cat MYSQLIMP.sql
[root@colinux tmp]# cat MYSQLIMP.txt
100 This is MYSQLIMPORT
101 STUDY AT HOME
102 HAVE A FUN
103 I WISH I CAN SLEEP..
[root@colinux tmp]#

特定のデータベースから、全てのオブジェクト作成DDLをバックアップ

[root@colinux tmp]# mysqldump --no-data --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review_3

[root@colinux tmp]# cat MYSQLIMP.sql
— MySQL dump 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)

— Host: localhost Database: STUDY
— ——————————————————
— Server version 5.1.39-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=” */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Table structure for table `MYSQLIMP`

DROP TABLE IF EXISTS `MYSQLIMP`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `MYSQLIMP` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`n` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on 2009-11-07 21:08:30
[root@colinux tmp]#

特定のデータベースから、全てのテーブルのデータとオブジェクト作成DDLをバックアップ

[root@colinux tmp]# mysqldump --tab=/tmp --lines-terminated-by="\r\n" STUDY -u root -p
Enter password:
[root@colinux tmp]#

mysql_dump_review_2

[root@colinux tmp]# cat MYSQLIMP.sql
— MySQL dump 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)

— Host: localhost Database: STUDY
— ——————————————————
— Server version 5.1.39-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=” */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Table structure for table `MYSQLIMP`

DROP TABLE IF EXISTS `MYSQLIMP`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `MYSQLIMP` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`n` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on 2009-11-07 20:59:38
[root@colinux tmp]# cat MYSQLIMP.txt
100 This is MYSQLIMPORT
101 STUDY AT HOME
102 HAVE A FUN
103 I WISH I CAN SLEEP..
[root@colinux tmp]#

※ --all-databasesなどで全部のDBをまとめて取得することも可能

——————————————————————————–
以下MYSQLDUMPのヘルプ
——————————————————————————–
[root@colinux tmp]# mysqldump

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

[root@colinux tmp]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.1.39, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory where character sets are.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-B, --databases To dump several databases. Note the difference in usage;
In this case no tables are given. All name arguments are
regarded as databasenames. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED;
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.
--ignore-table=database.table
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
--max_allowed_packet=#
--net_buffer_length=#
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
not be put in the output. The above line will be added
otherwise, if --databases or --all-databases option was
given.}.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 16777216
net_buffer_length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)

[root@colinux tmp]#


REPLACEオプションで指定したテーブルに対して、
まったく同じデータをIGNOREオプションを指定してLOADしてみました。

IGNORE を指定すると、 固有キー値上の、既存行を複製するインプット行はスキップされます。
もしどちらのオプションも指定しなければ、その動作は LOCAL キーワードが指定されたかどうかによって
決まります。LOCAL を利用すると、複製キー値が見つかった時点でエラーが発生し、テキスト ファイル
の残りは無視されます。LOCAL を使用しなければ、デフォルトの動作は IGNORE が指定された時と
同じです。これは、サーバは操作の最中にファイルの送信を中止する事ができないからです。

mysql> LOAD DATA INFILE '/tmp/MYSQLIMP.txt' IGNORE INTO TABLE MYSQLIMP;
Query OK, 0 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 4 Warnings: 0

メモ
①テキストファイルから4ラインが読み込まれた
②4件のデータが同じ値だったのでSKIPされた。(PKあり)

load_data_infile_ignore


REPLACE と IGNORE キーワードは、固有のキー値上に既存行を複製する
インプット行の扱いをコントロールします。

REPLACE を指定すると、インプット行は既存行を置き換えます。
言い換えると、主キーや固有インデックスに対して同じ値を持つ、既存行であるという事です。

IGNORE を指定すると、 固有キー値上の、既存行を複製するインプット行はスキップされます。
もしどちらのオプションも指定しなければ、その動作は LOCAL キーワードが指定されたかどうかによって
決まります。LOCAL を利用すると、複製キー値が見つかった時点でエラーが発生し、テキスト ファイル
の残りは無視されます。LOCAL を使用しなければ、デフォルトの動作は IGNORE が指定された時と
同じです。これは、サーバは操作の最中にファイルの送信を中止する事ができないからです。

もしロード操作中に外部キー制約を無視したければ、LOAD DATA を実行する前に
SET FOREIGN_KEY_CHECKS=0 ステートメントを発行する事ができます。

(例)
以下のテーブルにPKを作成後にREPLACEオプションを使用してデータをロードしてみます。

テーブルの状態
load_replace

テキストファイルの中身
text

mysql> LOAD DATA INFILE '/tmp/MYSQLIMP.txt' REPLACE INTO TABLE MYSQLIMP;
Query OK, 8 rows affected (0.00 sec)
Records: 4 Deleted: 4 Skipped: 0 Warnings: 0

メモ
①テキストファイルから4ラインが読み込まれた
②4件のデータが削除されて4件の新しいデータがファイルから読み込まれた。(PKあり)
③全体で、8行のデータが処理された(Query OK, 8 rows affected (0.00 sec))

REPLACE は、もしテーブル内の古い行が PRIMARY KEY か UNIQUE インデックスの
新しい行と同じ値を持っていれば、古い行は新しい行が挿入される前に削除されるという事以外、
INSERT と全く同じように機能します。

結果
load_data_infile_replace

参考サイト
LOAD DATA INFILE 構文
12.2.6. REPLACE 構文
LOAD DATA INFILE構文でデータのインポート!


LOAD DATA INFILEの他にMYSQLIMPORTプログラムを利用する事によって
データをIMPORTする事が出来ます。
mysqlimportクライアントはLOAD DATA INFILESQLステートメントにコマンドラインインターフェース
を提供します。 mysqlimportに対する殆どのオプションはLOAD DATA INFILE構文の節に直接対応
しています

mysqlimport_11

shell> mysqlimport [options] db_name textfile1 [textfile2 …]

コマンドラインで名づけられた各テキストファイルごとに、mysqlimportはファイルネームの
拡張を取り除き、結果をファイルの内容をインポートするテーブルの名前を決定します。
例えば、patient.txt、patient.text、そしてpatientと名づけられたファイルは全てpatientと
名づけられたファイルにインポートされます。

[root@colinux bin]# ./mysqlimport
./mysqlimport Ver 3.7 Distrib 5.1.39, for pc-linux-gnu (i686)
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command ‘LOAD DATA INFILE’ is used to import the rows.

Usage: ./mysqlimport [OPTIONS] database textfile…
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit
–no-defaults Don’t read default options from any options file
–defaults-file=# Only read default options from the given file #
–defaults-extra-file=# Read this file after the global files are read
–character-sets-dir=name
Directory where character sets are.
–default-character-set=name
Set the default character set.
-c, –columns=name Use only these columns to import the data to. Give the
column names in a comma separated list. This is same as
giving columns to LOAD DATA INFILE.
-C, –compress Use compression in server/client protocol.
-#, –debug[=name] Output debug log. Often this is ‘d:t:o,filename’.
–debug-check Check memory and open file usage at exit.
–debug-info Print some debug info at exit.
-d, –delete First delete all rows from table.
–fields-terminated-by=name
Fields in the textfile are terminated by …
–fields-enclosed-by=name
Fields in the importfile are enclosed by …
–fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by …
–fields-escaped-by=name
Fields in the i.file are escaped by …
-f, –force Continue even if we get an sql-error.
-?, –help Displays this help and exits.
-h, –host=name Connect to host.
-i, –ignore If duplicate unique key was found, keep old row.
–ignore-lines=# Ignore first n lines of data infile.
–lines-terminated-by=name
Lines in the i.file are terminated by …
-L, –local Read all files through the client.
-l, –lock-tables Lock all tables for write (this disables threads).
–low-priority Use LOW_PRIORITY when updating the table.
-p, –password[=name]
Password to use when connecting to server. If password is
not given it’s asked from the tty.
-P, –port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
–protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, –replace If duplicate unique key was found, replace old row.
-s, –silent Be more silent.
-S, –socket=name Socket file to use for connection.
–use-threads=# Load files in parallel. The argument is the number of
threads to use for loading data.
-u, –user=name User for login if not current user.
-v, –verbose Print info about the various stages.
-V, –version Output version information and exit.

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————–
character-sets-dir (No default value)
default-character-set utf8
columns (No default value)
compress FALSE
debug-check FALSE
debug-info FALSE
delete FALSE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
force FALSE
host (No default value)
ignore FALSE
ignore-lines 0
lines-terminated-by (No default value)
local FALSE
lock-tables FALSE
low-priority FALSE
port 3306
replace FALSE
silent FALSE
socket /tmp/mysql.sock
use-threads 0
user (No default value)
verbose FALSE
[root@colinux bin]#

[root@colinux bin]# mysql -e 'CREATE TABLE MYSQLIMP(id INT,n varchar(30))' STUDY -u root -p
Enter password:
[root@colinux bin]# ed
a
100 This is MYSQLIMPORT
101 STUDY AT HOME
102 HAVE A FUN
103 I WISH I CAN SLEEP..
.
w MYSQLIMP.txt
82
q
[root@colinux bin]# od MYSQLIMP.txt
0000000 030061 004460 064124 071551 064440 020163 054515 050523
0000020 044514 050115 051117 005124 030061 004461 052123 042125
0000040 020131 052101 044040 046517 005105 030061 004462 040510
0000060 042526 040440 043040 047125 030412 031460 044411 053440
0000100 051511 020110 020111 040503 020116 046123 042505 027120
0000120 005056
0000122
[root@colinux bin]# od -c MYSQLIMP.txt
0000000 1 0 0 \t T h i s i s M Y S Q
0000020 L I M P O R T \n 1 0 1 \t S T U D
0000040 Y A T H O M E \n 1 0 2 \t H A
0000060 V E A F U N \n 1 0 3 \t I W
0000100 I S H I C A N S L E E P .
0000120 . \n
0000122
[root@colinux bin]# mysqlimport --local STUDY MYSQLIMP.txt -u root -p
Enter password:
STUDY.MYSQLIMP: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
[root@colinux bin]# mysql -e 'select * from MYSQLIMP' STUDY -u root -p
Enter password:
+——+———————-+
| id | n |
+——+———————-+
| 100 | This is MYSQLIMPORT |
| 101 | STUDY AT HOME |
| 102 | HAVE A FUN |
| 103 | I WISH I CAN SLEEP.. |
+——+———————-+
[root@colinux bin]#

ed_command

※ edコマンド 「aから.まで」で、wでファイルに書き込みqでquit
編集しているときは、文字と文字はタブでスペースを空けてます。

参考サイト
7.14. mysqlimport — データインポートプログラム

MYSQLIMPORT



mysql> use STUDY
Database changed
mysql> SHOW CREATE TABLE Country\G
*************************** 1. row ***************************
Table: Country
Create Table: CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NO T NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

mysql>

特定の列にのみデータをロードしてみる
mysql> LOAD DATA LOCAL INFILE '/tmp/add_country.txt' INTO TABLE Country (Code,Name);

load_data_country

特定の列を指定してLOADしたので、指定した列にはデータがきちんと入っている。
NULLの列にはNULLが入り、それ以外でNULLを許容していない列にはDEFAULTの値が入っている。
aaa

DEFUALTの例

`Continent` enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’) NOT NULL DEFAULT ‘Asia’

※ LOAD DATA コマンドでテーブルにデータをロードするには、ファイルへのアクセス権限
MYSQLの”FILE Privilege“権限が必要です。

※ もっともシンプルな構文「 LOAD DATA INFILE ‘file_name’ INTO TABLE table_name; 」
   separator = (\t) と改行コード(\n)はdefaultなので省略可能。



CREATE TABLE `LOAD_DATA` (
`ID` int(11) DEFAULT NULL,
`FLAG` char(1) DEFAULT NULL,
`SDATE` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

imp_exp

以下のようにNULLと記入されているファイルをLOADすると以下のようになる。
[root@colinux tmp]# cat load.txt
NULL NULL NULL
[root@colinux tmp]#


LOAD DATA INFILE '/tmp/load.txt' INTO TABLE LOAD_DATA LINES TERMINATED BY '\r\n';

テキストの中身が「NULL NULL NULL」の場合
load_data_2

テキストの中身が「\N \N \N」の場合
null1
http://variable.jp/?p=344