ITPub博客

http://www.cnblogs.com/consatan/archive/2010/12/22/into_outfile_gbk.html
直接在命令行中SELECT显示无乱码，但使用 INTO OUTFILE 导出后有乱码，此时必须修改显示乱码的列的字符集

 

			

				CREATE TABLE `tab`(

  `id` INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  `name` VARCHAR(10) NOT NULL,

  `add` VARCHAR(20) CHARACTER SET gbk NOT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tab`(`name`,`add`) VALUES ('中文', '这列不会乱码'),

('汉字', '这列不会乱码'); SELECT * FROM `tab`; +----+------+--------------+  | id | name | add | +----+------+--------------+  | 1 | 中文 | 这列不会乱码 | | 2 | 汉字 | 这列不会乱码 | +----+------+--------------+  2 rows in set (0.00 sec) 
			

 

			

				SET @tmp_sql = CONCAT(
  "SELECT * INTO OUTFILE 'C:\\\\sql_", DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
  ".txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"'\" LINES TERMINATED BY '\r\n' FROM `tab`;");
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DROP PREPARE s1;
			

 

上面的代码会在C盘下生成“sql_当前时间.txt”的文件，避免INTO OUTFILE文件已存在错误

导出后的结果

 

将'name'列字符集修改为gbk

 

		

			ALTER TABLE `tab` CHANGE `name`   `name` VARCHAR(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL;
		

 

执行上面的导出命令，导出结果如下