MySQLに関するいくつかの注意

MySQLを使用している間、私は多くのニュアンスとトリックを蓄積しました。この記事をメモのセットとして作成しました。 これはすべて秘密ではなく、もちろんドキュメントに記載されています。



デフォルトのMySQL設定を使用します。 いくつかのメモはPHPに関連しているため、例としてmysqli拡張を使用します。



アーティクルからSQLクエリを実行するには、次のようにテーブルを初期化できます。

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);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次のように、現在の自動インクリメントを確認できます。

 SHOW TABLE STATUS;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

両方のテーブルの値は4です。同時に、データベースへのこのような接続があります。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一意のキーの挿入と自動インクリメント

テーブルに一意のキーがある場合、挿入および更新のために、一意性チェックをMySQLに移行する3つの方法があります。INSERTIGNORE、INSERT ... ON DUPLICATE KEY UPDATE、REPLACE。 各タイプのクエリは、異なるタイプのテーブルでの自動インクリメントにより異なる動作をします。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

自動挿入は5になりましたが、挿入はありませんでした。 そしてMyISAMはどうなりますか：

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

自動インクリメントは4のままでした。同様の状況が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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

user_innodbでは、自動インクリメントは6で、user_myisamは4です。



REPLACEの動作は異なります。一意のキーに一致するものがある場合、古いレコードを削除して新しいレコードを追加します。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ペトロフのid = 6になり、自動インクリメントが7に増加します。



MyISAMにも同じことがあります。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

自動インクリメントは5になり、ペトロフはid = 4になりました。



したがって、REPLACEは両方のテーブルエンジンで同じように機能しますが、INSERT IGNOREおよびON DUPLICATE KEY UPDATEは自動インクリメントをInnoDBに変更します。



自動インクリメントとInnoDBのニュアンスについて 。

更新後に可変レコードのIDを取得する

INSERT ... ON DUPLICATE KEY UPDATEを使用して挿入/更新した後、レコードが追加された場合にのみ$ mysqli-> insert_idにidが含まれます。 挿入または編集があったかどうかに関係なく、変更するレコードのIDを引き出す必要がある場合、これを行うことができます。

 $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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

7と7を印刷します。id= 7で初めてレコードが追加されたとき、2回目は変更されました。



INSERT IGNOREでは、このトリックは機能しません。 以下のコードは9と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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

シーケンスの実装

MySQLは、他のRDBMSとは異なり、SEQUENCEのようなものはありません。 自動インクリメントがありますが、SEQUENCEが役立つすべての問題を解決することはできません。 たとえば、シャーディング。



一般に、レコードを異なるテーブルまたはデータベースにソートする場合、自動インクリメントフィールドを持つマスターテーブルが必要になります。このフィールドでは、新しいレコードのIDが一元的に生成されます。



この問題は次のように解決できます。

 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);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

1つのテーブルでは、いくつかのシーケンスが一度に取得されます。この例では3つです。



次に、関数を使用して、目的のシーケンスから次のIDを取得できます。

last_insert_id（） ：

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

自動インクリメントの場合、auto_increment_increment構成オプションを使用してインクリメントステップを指定できます。 この例では、このような関数は次のように実装できます。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

符号なし整数について少し

PHPからデータベースにアクセスする場合は、MySQLフィールドのタイプとして符号なし整数を使用して精度を使用します。



このテーマに関する私の「洞察」の物語。 idフィールドには、常に符号なし整数型を使用しました。とにかく、古典的なidは決して負ではありません。 Gii（scaffolding Yii）を使用してモデルを生成すると、idおよびその他の符号なし整数フィールドのモデルの検証ルールが文字列に関して生成されることに気付きました。 「WTF？」-フレームワークのコードを考えてみましたが、フィールドタイプを解析するときに、符号なしをチェックするための「ハードコード」があることがわかりました。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは間違いだと思ったので、Yiiのバグの修正に貢献できるようになったことをうれしく思いました。 しかし、喜びはすぐに「これは、まあ、正当な理由だ」という考えに道を譲りました。



実際、PHPには符号なし整数はなく、一般に、PHPの整数は32ビットです（32ビットLinuxおよびWindowsの場合）。 整数値がPHP_INT_MAXを超える場合、浮動小数点に変換されます。ここに、奇妙なバグが発生する魔法の場所があります。 それで、 Qiang Xue氏は正しいことをした。