в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:49:05Z

1 версия

Новая страница

== Exporting Data==

The command to export data to a text file is similar to the standard SELECT command.
MySQL creates a "tab delimited" file by default.



<source lang="sql">
SELECT <columns>
FROM <table_name>
INTO OUTFILE <file_name></source>

== Importing data==

Importing data is a very simliar process to exporting.
The syntax for importing a file is:



<source lang="sql">
LOAD DATA INFILE "file_name.txt"
INTO TABLE tbl_name (field1, field2...etc)</source>

== LOAD DATA INFILE into a table==



<source lang="sql">
mysql>
mysql>
mysql> CREATE TABLE Topic(
-> TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(50) NOT NULL,
-> InStock SMALLINT UNSIGNED NOT NULL,
-> OnOrder SMALLINT UNSIGNED NOT NULL,
-> Reserved SMALLINT UNSIGNED NOT NULL,
-> Department ENUM("Classical", "Popular") NOT NULL,
-> Category VARCHAR(20) NOT NULL,
-> RowUpdate TIMESTAMP NOT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO Topic (Name, InStock, OnOrder, Reserved, Department, Category) VALUES
-> ("Java", 10, 5, 3, "Popular", "Rock"),
-> ("JavaScript", 10, 5, 3, "Classical", "Opera"),
-> ("C Sharp", 17, 4, 1, "Popular", "Jazz"),
-> ("C", 9, 4, 2, "Classical", "Dance"),
-> ("C++", 24, 2, 5, "Classical", "General"),
-> ("Perl", 16, 6, 8, "Classical", "Vocal"),
-> ("Python", 2, 25, 6, "Popular", "Blues"),
-> ("Php", 32, 3, 10, "Popular", "Jazz"),
-> ("ASP.net", 12, 15, 13, "Popular", "Country"),
-> ("VB.net", 5, 20, 10, "Popular", "New Age"),
-> ("VC.net", 24, 11, 14, "Popular", "New Age"),
-> ("UML", 42, 17, 17, "Classical", "General"),
-> ("www.sqle.ru",25, 44, 28, "Classical", "Dance"),
-> ("Oracle", 32, 15, 12, "Classical", "General"),
-> ("Pl/SQL", 20, 10, 5, "Classical", "Opera"),
-> ("Sql Server", 23, 12, 8, "Classical", "General");
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from Topic;
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| TopicID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| 1 | Java | 10 | 5 | 3 | Popular | Rock | 2007-07-23 19:09:52 |
| 2 | JavaScript | 10 | 5 | 3 | Classical | Opera | 2007-07-23 19:09:52 |
| 3 | C Sharp | 17 | 4 | 1 | Popular | Jazz | 2007-07-23 19:09:52 |
| 4 | C | 9 | 4 | 2 | Classical | Dance | 2007-07-23 19:09:52 |
| 5 | C++ | 24 | 2 | 5 | Classical | General | 2007-07-23 19:09:52 |
| 6 | Perl | 16 | 6 | 8 | Classical | Vocal | 2007-07-23 19:09:52 |
| 7 | Python | 2 | 25 | 6 | Popular | Blues | 2007-07-23 19:09:52 |
| 8 | Php | 32 | 3 | 10 | Popular | Jazz | 2007-07-23 19:09:52 |
| 9 | ASP.net | 12 | 15 | 13 | Popular | Country | 2007-07-23 19:09:52 |
| 10 | VB.net | 5 | 20 | 10 | Popular | New Age | 2007-07-23 19:09:52 |
| 11 | VC.net | 24 | 11 | 14 | Popular | New Age | 2007-07-23 19:09:52 |
| 12 | UML | 42 | 17 | 17 | Classical | General | 2007-07-23 19:09:52 |
| 13 | www.sqle.ru | 25 | 44 | 28 | Classical | Dance | 2007-07-23 19:09:52 |
| 14 | Oracle | 32 | 15 | 12 | Classical | General | 2007-07-23 19:09:52 |
| 15 | Pl/SQL | 20 | 10 | 5 | Classical | Opera | 2007-07-23 19:09:52 |
| 16 | Sql Server | 23 | 12 | 8 | Classical | General | 2007-07-23 19:09:52 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
16 rows in set (0.00 sec)
mysql>
mysql> CREATE TABLE Topic3
-> (
-> Name VARCHAR(50) NOT NULL,
-> InStock SMALLINT UNSIGNED NOT NULL,
-> Category VARCHAR(20)
-> );
mysql>
mysql>
mysql> SELECT Name, InStock, Category INTO OUTFILE "TopicCountry.sql"
-> FIELDS
-> TERMINATED BY ","
-> ENCLOSED BY """
-> FROM Topic WHERE Category="Country";
mysql>
mysql>
mysql> LOAD DATA INFILE "TopicCountry.sql"
-> INTO TABLE Topic3
-> FIELDS
-> TERMINATED BY ","
-> ENCLOSED BY """;
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> drop table Topic;
Query OK, 0 rows affected (0.00 sec)</source>

== Load data to your table==

Create a text file myTable.txt containing one record per line.
The values are separated by tabs.
The order should be consistant with the columns listed in the CREATE TABLE statement.
For NULL values, use \N (backslash, capital-N).
For example:



<source lang="sql">
1 firstName lastName M \N 1997-12-09</source>

== Turn Exporting and Importing Data on==

You should be able to turn this feature on by issuing the command:



<source lang="sql">
local-infile=1</source>

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/MySQL Utilities/Import Export Data - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия