public
Created

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
# Given this data in a file named "test_data.txt" (10 characters each field):
 
test_a 1 1.1
test_b 2 2.2
test_c 3 3.3
 
# The following commands will load it into columns:
 
# Clean up these test tables so the script can be re-run.
DROP TABLE IF EXISTS test_fixed, test_fields;
 
# Create a table with all CHAR fields with the correct length for each field.
CREATE TABLE test_fixed (
c_string CHAR(10) NOT NULL,
c_int CHAR(10) NOT NULL,
c_float CHAR(10) NOT NULL
);
 
# Create a table with the correct field types.
CREATE TABLE test_fields (
c_string CHAR(10) NOT NULL,
c_int INT NOT NULL,
c_float FLOAT NOT NULL
);
 
# Load the data into the fixed-width strings table.
LOAD DATA LOCAL
INFILE 'test_data.txt'
INTO TABLE test_fixed
FIELDS TERMINATED BY '';
 
# Copy the data from the strings to the right types. MySQL will auto-convert types,
# but if you need to do anything special for conversion, or for things MySQL can't
# handle automatically, just do it in the SELECT part of the query.
INSERT INTO test_fields SELECT * FROM test_fixed;
 
# Clean up the temp table.
DROP TABLE test_fixed;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.