Study Guide: MySQL->Language->Import
MySQL/Language/Import/export

Aside from mysqldump (cf. MySQL/Administration), you can also export / import raw data.

Export data

Data can be exported using the "INTO OUTFILE" keyword

 SELECT * FROM destinataire INTO OUTFILE '/tmp/test' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211);

Beware that the MySQL daemon itself will write the file, not the user you run the MySQL client with. The file will be stored on the server, not on your host. Moreover, the server will need write access to the path you specify (usually, the server can _not_ write in your home directory, e.g.). Hence why we (unsecurely) used /tmp in the examples.

You can also use the command line to export data

mysql < query.txt > output.txt

where query.txt contains an sql-query and the output will be stored in output.txt

Import data

In another database/computer/etc. the data can be imported:

 LOAD DATA INFILE '/tmp/test' INTO TABLE destinataire;

additional options are

 FIELDS TERMINATED BY '\t'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES

to specify how the document is set up and whether there is a header. The columns in the data file can be mapped to the columns of the database table if they do not correspond and it is thus also possible to omit certain columns using a dummy variable:

 LOAD DATA LOCAL INFILE
 '/tmp/test'
 INTO TABLE destinataire 
 FIELDS TERMINATED BY '\t'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES 
 ( 
 @dummy,
 name,
 phone_number,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy
 )

In this example, we only need the second and third column of the data file and store these values in the name and phone_number column of our database table.

Content precisions

To import a .sql which creates a user and its database, one should know if this user already exists on the server, because MySQL doesn't have any DROP USER IF EXISTS. On the contrary it works with the databases:

DROP DATABASE IF EXISTS `base1`;
CREATE DATABASE `base1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `base1`;
--DROP USER `user1`@'localhost';
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'p@ssword1';
GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'p@ssword1';
GRANT ALL PRIVILEGES ON `user1`.* TO 'user1'@'localhost';

PS: if this commande returns "DROP DATABASE" statements are disabled with PhpMyAdmin, modify config.default.php by switching $cfg['AllowUserDropDatabase'] to true, and empty the navigator cache.


  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

MySQL/Language/Import/export
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.

Visit defaultLogic's partner sites below:
PopFlock.com : Music Genres | Musicians | Musical Instruments | Music Industry
NCR Works : Retail Banking | Restaurant Industry | Retail Industry | Hospitality Industry

  Contact Us