Menu
In PHP, I create and execute SQL queries like the following.
INSERT INTO Table (Column1, Column2, Column3) VALUES
('1', '2', '3'),
('A', 'B', 'C'),
('AA', 'BB', 'CC');
However, the order in which they are inserted into the database is different every time I try this. Is there a way that I can ensure that they are inserted in the order they are listed?
Addition: Thanks guys for the help. I have using PHP to create MySQL tables from CSV files for a while. In the past, I have always used created a table and inserted all the rows all at once. In these case, the SQL table always had the same order as my INSERT query. However, now I am creating a MySQL and then adding contents gradually. This is when the database order becomes random.
I have overcome this by using ALTER TABLE ... ORDER BY queries, but I am curious as there was order in the first case and now it seems very random.
970
The only thing that order by on an insert is guaranteed to do is assign the values of an identity column if one exists. Your select has no order by, hence SQL is in no way required to return the data in any particular order.
An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations.
If you're not specifying column names, then ordering matters (you must INSERT in the same order that the table is structured). If you are specifying the column names, the order doesn't matter. For example: INSERT INTO TABLE_NAME VALUES ('','','') // Here the values needs to be in order of columns present in your table.
The default order is what the order of insert statements executed. Unless there's a hierarchical relationship between the rows, the inserted order is irrelevant. If you want the output in a consistent fashion, you must define an ORDER BY clause in your query.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
