Posts Tagged ‘load’

remember ‘character set utf8′ while loading data into mysql

March 10th, 2011

Recently, I tried to load some data which contains Chinese characters into Mysql. The data is saved in a txt file separated by ‘t’ and encoded with utf8. My mysql table also has the right encoding utf8.

I knew I had to set the char encoding right to be utf8, so I used ‘set names utf8′ in the following command to load the data:

set names utf8;
LOAD DATA LOCAL INFILE '/home/me/datadump/my_data.txt'
INTO TABLE my_table;

However, it turns out this doesn’t work. When checking the data in a terminal, I can get the right results without ‘set names utf8′, but error coded results with ‘set names utf8′. Of course, when using this data in utf8 encoded application, I also got the error results.

After searching, I found the right way to load data is:

LOAD DATA LOCAL INFILE '/home/me/datadump/my_data.txt'
INTO TABLE my_table CHARACTER SET UTF8;

Export results from MySQL into an external file

February 10th, 2010

1. From mysql command line:

  • SELECT colA, colB FROM table1 INTO OUTFILE "/tmp/output.txt";

The detail SELECT syntax is:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

2. From console: combine mysql logging in & exporting

  • echo "SELECT colA, colB FROM table1" | mysql -uXXX -pYYY SchemaName > /tmp/outout.txt;
  • mysql mysql -uXXX -pYYY SchemaName < /tmp/export.sql > /tmp/outout.txt;

REFERENCE:

——————————————————————————————–

The corresponding syntax of loading outfile data is:

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]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

REFERENCE: