CODEWITHSUNDEEP
mysqlinsert
nerdiplayboy
nerdiplayboy

Reputation: 556

Mysql-If I insert multiple values in a column of a table simultaneously ,is it possible that the inserting orders of values get change?

I am doing these :

insert into table_name(maxdate) values 
((select max(date1) from table1)), -- goes in row1
((select max(date2) from table2)), -- goes in row2
.
.
.
((select max(date500) from table500));--goes in row500

is it possible that while insertion , order of inserting might get change ?.Eg when i will do 

select maxdate from table_name limit 500;

i will get these date1 date2 . . date253 date191 ...date500

Upvotes: 0

Views: 304

Answers (2)

aconrad
aconrad

Reputation: 586

Short answer:

No, not possible.

If you want to double check :

mysql> create table letest (f1 varchar(50), f2 varchar(50));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into letest (f1,f2) values 
( (SELECT SLEEP(5)), 'first'), 
( (SELECT SLEEP(1)), 'second');
Query OK, 2 rows affected, 1 warning (6.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from letest;
+------+--------+
| f1   | f2     |
+------+--------+
| 0    | first  |
| 0    | second |
+------+--------+
2 rows in set (0.00 sec)

mysql>

SLEEP(5) is the first row to be inserted after 5 seconds,

SLEEP(1) is the second row to be inserted after 5+1 seconds

that is why query takes 6 seconds.

The warning that you see is 

mysql> show warnings;
+-------+------+-------------------------------------------------------+
| Level | Code | Message                                               |
+-------+------+-------------------------------------------------------+
| Note  | 1592 | Statement may not be safe to log in statement format. |
+-------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

This can affect you only if you are using a master-slave setup, because the replication binlog will not be safe. For more info on this http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html

Later edit: Please consider a comment if you find this answer not usefull.

Upvotes: 1

Konerak
Konerak

Reputation: 39763

Yes, very possible.

You should consider a database table unordered, and a SELECT statement without ORDER clause as well. Every DBMS can choose how to implement tables (often even depending on Storage Engine) and return the rows. Sure, many DBMS's happen to return your data in the order you inserted, but never rely on it.

The order of the retrieved data my depend on the execution plan, and may even be different when running the same query multiple times. Especially when only retrieving part of the data (TOP/LIMIT).

If you want to impose an order, add a field which orders your data. Yes, an autoincrement primary key will be enough in many cases. If you think you'll be wanting to change the order someday, add another field.

Upvotes: 1

Related Questions