A few notes about MySQL

During the work with MySQL, a certain amount of nuances and tricks were gathered, of which I composed this article in the form of a set of notes. All this is not a secret and, of course, can be found in the documentation.

I will use the default MySQL settings. Some notes are related to PHP, so for the examples I will use the mysqli extension.

To run sql queries from an article, you can initialize the tables as follows:
')

CREATE TABLE IF NOT EXISTS `user_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(8) NOT NULL, `money` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `login` (`login`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; INSERT INTO `user_myisam` (`id`, `login`, `money`) VALUES (1, 'ivanov', 100), (2, 'petrov', 200), (3, 'sidorov', 300); CREATE TABLE IF NOT EXISTS `user_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(8) NOT NULL, `money` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `login` (`login`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; INSERT INTO `user_innodb` (`id`, `login`, `money`) VALUES (1, 'ivanov', 100), (2, 'petrov', 200), (3, 'sidorov', 300); 

You can check current auto increments like this:

 SHOW TABLE STATUS; 

In both tables they are equal to 4. At the same time, there is such a connection to the database:

 $mysqli = new mysqli($host, $user, $password, $database); 

Insert by unique key and auto-increment

If the table has a unique key, then for insertion and updating there are three ways to pass the uniqueness check on MySQL: INSERT IGNORE, INSERT ... ON DUPLICATE KEY UPDATE, REPLACE. Each type of query behaves differently with auto-increment on different types of tables:

 INSERT IGNORE INTO `user_innodb` SET `login` = "ivanov", `money` = 1000; 

Autoincrement became 5, although there was no insert. And what will happen to MyISAM:

 INSERT IGNORE INTO `user_myisam` SET `login` = "ivanov", `money` = 1000; 

There the auto increment remained 4. A similar situation will be with ON DUPLICATE KEY UPDATE:

 INSERT INTO `user_innodb` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; INSERT INTO `user_myisam` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; 

In user_innodb, autoincrement became 6, and user_myisam remained 4.

REPLACE works differently: in case of finding matches in a unique key, it will delete the old record and add a new one.

 REPLACE `user_innodb` SET `login` = "petrov", `money` = 2000; 

Increments the auto increment to 7, now Petrov has id = 6.

MyISAM is similar:

 REPLACE `user_myisam` SET `login` = "petrov", `money` = 2000; 

Autoincrement became 5, and Petrov got id = 4.

So, REPLACE works on both table engines in the same way, and INSERT IGNORE and ON DUPLICATE KEY UPDATE change the autoincrement to InnoDB.

About autoincrement and about nuances with InnoDB .

Getting the id of the record being modified after the update

After inserting / updating, using INSERT ... ON DUPLICATE KEY UPDATE, $ mysqli-> insert_id contains the id only if an entry was added. If you need to pull out the id of the record being modified, regardless of whether it was inserted or edited, you can do this:

 $mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 10;'); echo $mysqli->insert_id; $mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 20;'); echo $mysqli->insert_id; 

Displays 7 and 7, the first time the entry was added under id = 7, the second time changed.

With INSERT IGNORE such a trick will not work. The code below will display 9 and 0

 $mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`), `login` = "smith", `money` = 3000'); echo $mysqli->insert_id; $mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`),`login` = "smith", `money` = 5000'); echo $mysqli->insert_id; 

SEQUENCE implementation

MySQL, unlike other RDBMS, does not have such a thing as SEQUENCE. There is an auto-increment, but it does not allow to solve all the tasks that SEQUENCE can help with. For example, sharding.

In general, if you need to arrange records on different tables or even databases, you will need a master table with an auto-increment field in which the ID of a new record would be centrally generated.

You can solve this problem like this:

 CREATE TABLE IF NOT EXISTS `sequence` ( `user_id` int(11) NOT NULL DEFAULT '0', `post_id` int(11) NOT NULL DEFAULT '0', `some_other_id` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sequence` (`user_id`, `post_id`, `some_other_id`) VALUES (0, 0, 0); 

In one table, several sequences are obtained at once, in this example, three.

Then you can get the following ID from the desired sequence using the function
last_insert_id () :

 UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + 1); SELECT last_insert_id(); 

For auto increment, it is possible to specify the increment step with the auto_increment_increment configuration option. In this example, such a function can be implemented like this:

 UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + N); SELECT last_insert_id(); 

Little about unsigned integers

With care, use unsigned integers as MySQL field types if you access the database from PHP.

The story of my "insight" about this. For the id field, I always used an unsigned integer type, still the classic id is not negative. Once, when generating a model using Gii (Yii scaffolding), I noticed that the validation rules in the model for my id and other unsigned integer fields are generated as for strings. “WTF?” - I thought and got into the framework code, where I found that when parsing field types there is such a “hardcode” of checking for unsigned:

 if(stripos($dbType,'int')!==false && stripos($dbType,'unsigned int')===false) 

I considered this a mistake, I was glad that now I have a chance to contribute to the correction of the Yii bugs. But the joy was quickly replaced by the thought “it’s not for nothing”.

Indeed, in PHP there are no unsigned integers, but in general there are 32-bit integers in PHP (under 32-bit Linux and under Windows). If the integer value exceeds PHP_INT_MAX, then it is cast to float, and then the place for the emergence of magic with strange bugs. So Mr. Qiang Xue did the right thing.