1

I'm importing a CSV of sequential data into a MySQL database. Unfortunately, the sequential data isn't in sequence (for instance - a1, j10, k11, b2, c3, d4, e5, f6, g7, h8, i9).

As such, I don't want to add keys in the order that they currently are. I would want to order them, appropriately, and then add keys.

Now, I know that the order isn't that important. Humor me that I'm anal-retentive. This data should never change, so I'd like it to be sequential if sorted by the key column.

Is there a way to create a query and do all the key stuff at the same time?

This StackOverflow question showed me the code to add the column, but that wouldn't be in the right order.

What I would like to do is implement that into an ORDER BY query.

Improve this question
7
  • What's the sequence? Commented Jun 29, 2017 at 16:58
  • Why not using the characteristic field of the sequence as the primary key? That's what primary keys are for. Commented Jun 29, 2017 at 17:02
  • @Strawberry It's more like 1 1 1, 1 1 2, 1 2 1, 1 2 2, 2 1 1, 2 1 2, 2 2 1, 2 2 2. where each number is a separate column and each comma is a separate row. Commented Jun 29, 2017 at 17:40
  • @nCessity Unfortunately, none of the columns are unique. That's what I wanted to add another one. Commented Jun 29, 2017 at 17:41
  • 1
    See: Why should I provide an MCVE for what seems to me to be a very simple SQL query? Commented Jun 29, 2017 at 17:45

2 Answers 2

Reset to default
1

This is maybe not exactly the solution you had in mind, but I have reason to believe that it is the best solution for your data (assuming you gave us all relevant information).

It seems you wanna have a single primary key column in your table. Like an auto-increment integer or so. But your data provides the primary key itself. The unique tuple of columns in your input file. There is no need (for literally any query) for another column. Every information is there without it, it would only take some more space on your disc (and even worse, your ram).

Here's what I'd do: Let the primary key of your table cover the unique tuples of columns which make the order of your sequence. And then just load your data.

This example works for me:

create TABLE test_table (col1 VARCHAR(255) NOT NULL, col2 VARCHAR(255) NOT NULL, col3 VARCHAR(255) NOT NULL, PRIMARY KEY (col1, col2, col3));

LOAD DATA INFILE 'test_data.tsv' INTO TABLE test_table (col1, col2, col3);

test_data.tsv:

2   1   1  
1   1   2  
2   1   2  
1   2   1  
1   2   2  
2   2   1  
2   2   2  
1   1   1

Everything is in order if I do

SELECT * FROM test_table ORDER BY col1, col2, col3;

Is the slightly longer (,col2,col3) query which you would have to write a problem? Create a view or a stored procedure! Don't bloat your table with a completely redundant column.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I wasn't aware that you could create a primary key across multiple columns. Not adding another column makes sense, now that I have that knowledge.
Based on your guidance, I used an edit of this code... ALTER TABLE tablename ADD PRIMARY KEY(column1, column2, column3); and it was successful.
I've been doing some further reading about keys and I'm, now, wondering if there was any point in adding it. It seems that the index is what gives a performance benefit. MySQL with the InnoDB engine adds the index with the key, but my database uses MyIASM. A primary key has the benefit of being used in a foreign key situation, but MyIASM doesn't seem to support that. Was there any usefulness to adding the primary key, in my case? It does give the ability to not have duplicate records, but no records should ever be added to these tables. They are, strictly, for lookup.
@doubleJ: Still, it's logically right to have these columns as a primary key. And depending on your number of rows, you should see the performance boost when doing 'SELECT * FROM test_table ORDER BY col1, col2, col3;` because a primary key is allways a unique index. That means, MySQL wont scan the full table but use the key.
The tables, in question, are 30000ish records.
|
0

Import, run order by and place in the "ordered" table. Keep in mind that SQL is per definition not returning ordered data, so even when getting your table ordered, you will not be guaranteed any ordered output even when running a straightselect * from table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.