Skip to content

Instantly share code, notes, and snippets.

@cyshallchan
Created July 16, 2020 03:08
Show Gist options
  • Save cyshallchan/c6f4f866b57de052e54bef1d050ccfd5 to your computer and use it in GitHub Desktop.
Save cyshallchan/c6f4f866b57de052e54bef1d050ccfd5 to your computer and use it in GitHub Desktop.
mysql export data with coloumn name
SET @table_name = "user";
SET @schema_name = "test_db";
SET @output_name = CONCAT("/var/lib/mysql-files/",@table_name,".txt");
SET @cols = NULL;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")) INTO @cols
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @schema_name;
SET @sql = CONCAT(" SELECT * FROM (SELECT ", @cols, " UNION ALL SELECT * FROM ", @table_name, ") as r ",
" INTO OUTFILE '", @output_name,
"' FIELDS TERMINATED BY '\\t' ");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment