Borja Borja - 1 month ago 6
MySQL Question

Why when uploading a csv to mysql, data changes order?

Why when uploading a csv to mysql, data changes order ?

I created this table:

CREATE TABLE earthquakes (
idserial VARCHAR(30),
milliseconds BIGINT,
latitude FLOAT,
longitude FLOAT,
magnitude FLOAT,
ipocentro FLOAT,
source VARCHAR(4),
region SMALLINT,
PRIMARY KEY(idserial),
INDEX indice_milliseconds(milliseconds),
INDEX indice_lat_lng(latitude,longitude),
INDEX indice_magnitude(magnitude),
INDEX indice_ipocentro(ipocentro)
);


In my
csv
file data is are in this order:

"1900-07-29-1-0",-2190913260000,-10,165,7.6,0,"U",184
"1900-10-09-1-0",-2184672720000,60,-142,7.7,0,"U",19
"1900-10-29-1-0",-2182952940000,11,-66,7.7,0,"U",94
"1901-08-09-1-0",-2158405140000,-22,170,7.9,0,"U",189
"1901-08-09-2-0",-2158385220000,40,144,7.5,0,"U",229
"1902-04-19-1-0",-2136584220000,14,-91,7.5,0,"U",70
"1902-08-22-1-0",-2125782000000,40,77,7.7,0,"U",320
"1902-09-22-2-0",-2123108040000,18,146,7.5,0,"U",216
"1902-09-23-1-0",-2122954920000,16,-93,7.8,0,"U",61
"1903-01-14-1-0",-2113254780000,15,-98,7.7,0,"U",66
"1904-06-25-1-0",-2067592461000,51.565,161.417,7.5,15,"U",219


The records are sorted from the oldest date to the most recent. But after loading is so:

"1904-06-25-1-0",-2067592461000,51.565,161.417,7.5,15,"U",219
"1903-01-14-1-0",-2113254780000,15,-98,7.7,0,"U",66
"1902-09-23-1-0",-2122954920000,16,-93,7.8,0,"U",61
"1902-09-22-2-0",-2123108040000,18,146,7.5,0,"U",216
"1902-08-22-1-0",-2125782000000,40,77,7.7,0,"U",320
"1902-04-19-1-0",-2136584220000,14,-91,7.5,0,"U",70
"1900-07-29-1-0",-2190913260000,-10,165,7.6,0,"U",184
"1900-10-09-1-0",-2184672720000,60,-142,7.7,0,"U",19
"1900-10-29-1-0",-2182952940000,11,-66,7.7,0,"U",94
"1901-08-09-1-0",-2158405140000,-22,170,7.9,0,"U",189
"1901-08-09-2-0",-2158385220000,40,144,7.5,0,"U",229


They doesn't respect the initial order....

Could be that i have like
PRIMARY KEY
the field
idserial
(first value) that is a string (unique) and not numeric value with autoincrement ?

Answer Source

SQL tables represent unordered sets. If you want data in a particular order, then you need an order by clause when you query the table.

If you want to add an auto-incremented id column (which I recommend), then you can have that column preserve the original ordering in the CSV file. Just create the table with that column and list all other columns in the load data statement.