into table db.new
fields terminated by ':' //列的分割符
lines terminated by '\n'; //行的分割符
load data local infile '/tmp/test.txt'
into table db.user //表列类似/etc/passwd，自己动手建吧！
fields terminated by ':'
lines terminated by '\n'
(username, password, uid, gid, common, home_dir, shell) ;
从数据库导出到文档 mysql> select username,password,uid from db.new into outfile '/tmp/tao.txt'
fields terminated by '#'
lines terminated by '\n';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql之load dataName: 'LOAD DATA'
Description:
Syntax:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
The LOAD DATA INFILE statement reads rows from a text file into a table
at a very high speed. The file name must be given as a literal string.
LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See
http://dev.mysql.com/doc/refman/5.5/en/select-into.html.) To write data
from a table to a file, use SELECT ... INTO OUTFILE. To read the file
back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and
LINES clauses is the same for both statements. Both clauses are
optional, but FIELDS must precede LINES if both are specified.
For more information about the efficiency of INSERT versus LOAD DATA
INFILE and speeding up LOAD DATA INFILE, see
http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html.
The character set indicated by the character_set_database system
variable is used to interpret the information in the file. SET NAMES
and the setting of character_set_client do not affect interpretation of
input. If the contents of the input file use a character set that
differs from the default, it is usually preferable to specify the
character set of the file by using the CHARACTER SET clause. A
character set of binary specifies "no conversion."
LOAD DATA INFILE interprets all fields in the file as having the same
character set, regardless of the data types of the columns into which
field values are loaded. For proper interpretation of file contents,
you must ensure that it was written with the correct character set. For
example, if you write a data file with mysqldump -T or by issuing a
SELECT ... INTO OUTFILE statement in mysql, be sure to use a
--default-character-set option with mysqldump or mysql so that output
is written in the character set to be used when the file is loaded with
LOAD DATA INFILE.
*Note*: It is not possible to load data files that use the ucs2, utf16,
or utf32 character set.
The character_set_filesystem system variable controls the
interpretation of the file name.
You can also load data files by using the mysqlimport utility; it
operates by sending a LOAD DATA INFILE statement to the server. The
--local option causes mysqlimport to read data files from the client
host. You can specify the --compress option to get better performance
over slow networks if the client and server support the compressed
protocol. See http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html.
If you use LOW_PRIORITY, execution of the LOAD DATA statement is
delayed until no other clients are reading from the table. This affects
only storage engines that use only table-level locking (such as MyISAM,
MEMORY, and MERGE).
If you specify CONCURRENT with a MyISAM table that satisfies the
condition for concurrent inserts (that is, it contains no free blocks
in the middle), other threads can retrieve data from the table while
LOAD DATA is executing. Using this option affects the performance of
LOAD DATA a bit, even if no other thread is using the table at the same
time.
Prior to MySQL 5.5.1, CONCURRENT was not replicated when using
statement-based replication (see Bug #34628). However, it is replicated
when using row-based replication, regardless of the version. See
http://dev.mysql.com/doc/refman/5.5/en/replication-features-load-data.h
tml, for more information.
The LOCAL keyword, if specified, is interpreted with respect to the
client end of the connection:
o If LOCAL is specified, the file is read by the client program on the
client host and sent to the server. The file can be given as a full
path name to specify its exact location. If given as a relative path
name, the name is interpreted relative to the directory in which the
client program was started.
When using LOCAL with LOAD DATA, a copy of the file is created in the
server's temporary directory. This is not the directory determined by
the value of tmpdir or slave_load_tmpdir, but rather the operating
system's temporary directory, and is not configurable in the MySQL
Server. (Typically the system temporary directory is /tmp on Linux
systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for
the copy in this directory can cause the LOAD DATA LOCAL statement to
fail.
o If LOCAL is not specified, the file must be located on the server
host and is read directly by the server. The server uses the
following rules to locate the file:
o If the file name is an absolute path name, the server uses it as
given.
o If the file name is a relative path name with one or more leading
components, the server searches for the file relative to the
server's data directory.
o If a file name with no leading components is given, the server
looks for the file in the database directory of the default
database.
Note that, in the non-LOCAL case, these rules mean that a file named as
./myfile.txt is read from the server's data directory, whereas the file
named as myfile.txt is read from the database directory of the default
database. For example, if db1 is the default database, the following
LOAD DATA statement reads the file data.txt from the database directory
for db1, even though the statement explicitly loads the file into a
table in the db2 database:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
Windows path names are specified using forward slashes rather than
backslashes. If you do use backslashes, you must double them.
For security reasons, when reading text files located on the server,
the files must either reside in the database directory or be readable
by all. Also, to use LOAD DATA INFILE on server files, you must have
the FILE privilege. See
http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html. For
non-LOCAL load operations, if the secure_file_priv system variable is
set to a nonempty directory name, the file to be loaded must be located
in that directory.
http://dev.mysql.com/doc/refman/5.5/en/load-data.html
Share两个数据库之间相互导入，需要字符集一样!如果是英文的字符集，则无需关注字符集! 字符集相关，分享一个Oracle大师的研究！太厉害了！
(windows \n\r , linux \n ========>shell>yum install -y
dos2unix)
shell>dos2unix test.txt
--Concept:This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
--记录完整的sql,但sql被分片存储
/*
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER --address、hash_value唯一标识一条sql
SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
PIECE NUMBER --PIECE分片之后的顺序
SQL_TEXT VARCHAR2(64) --SQL_TEXT为sql文本
*/
select * from v$sqltext;
--v$sqlarea
--Concept:V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
--记录共享sql区中的统计信息(执行次数、逻辑读、物理读等);相同文本的sql只记录一次
/*
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(76)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(797)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID VARCHAR2(40)
LAST_ACTIVE_CHILD_ADDRESS RAW(4)
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME DATE
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
*/
select * from v$sqlarea
--v$sql
--Concept:V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
--记录共享sql区中的统计信息(执行次数、逻辑读、物理读等);每一次执行sql语句都会记录，且与v$sql_plan关联可查询每条sql语句的执行计划。
1.SQLite 缺省为每个操作启动一个事务，那么如果进行1000次操作则要开启1000个事务，"事务开启 + SQL 执行 + 事务关闭"自然耗费了大量的时间。SQLite的数据库本质上来讲就是一个磁盘上的文件，所以一切的数据库操作其实都会转化为对文件的操作，而频繁的文件操作将会是一个很好时的过程，会极大地影响数据库存取的速度。而使用事务将把全部要执行的SQL语句先缓存在内存当中，然后等到COMMIT的时候一次性的写入数据库，这样数据库文件只被打开关闭了一次，效率自然大大的提高。
本文链接