Setting a unique key on a table influences the order by of a SELECT * FROM thetable
Say I have
Code: Select all
CREATE TABLE `users` (
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) NOT NULL,
`user_firstname` varchar(255) NOT NULL,
`user_lastname` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;
Now a "SELECT * FROM users" will order the results by user_id
Code: Select all
CREATE TABLE `users` (
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) NOT NULL,
`user_firstname` varchar(255) NOT NULL,
`user_lastname` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY (`user_name`, `user_firstname`,`user_lastname`)
) ENGINE=InnoDB;
Now a "SELECT * FROM users" will order the results by user_name.
Why is that?
Or is there no why and is this just the way mysql handles things? I didn't know about this behavior. led to some unexpected errors in my Unit tests..