Comment résoudre le problème d'horodatage 2038 de Mysql

L'horodatage fait référence au nombre total de secondes depuis 00:00:00 GMT le 1er janvier 1970 (08:00:00 le 1er janvier 1970, heure de Pékin) jusqu'à aujourd'hui.
Différentes versions de MySQL sont déployées dans l'environnement de production, dont trois versions majeures de MySQL 5.5/5.6/5.7 et N versions mineures. En raison de la mauvaise compatibilité ascendante de MySQL, le même SQL se comporte différemment dans différentes versions, comme suit L'horodatage. Le type de données est présenté en détail sous plusieurs aspects.

Accès aux données d'horodatage

Dans les trois versions majeures ci-dessus de MySQL, la plage de valeurs du type d'horodatage par défaut (Timestamp) va de ’1970-01-01 00:00:01’ 19 03:14:07’ UTC, les données sont précises au deuxième niveau. Cette plage de valeurs contient environ 2,2 milliards de valeurs, donc le type INT à 4 octets est utilisé en interne dans MySQL pour stocker les données d'horodatage :
1. Lors de l'horodatage des données, convertissez d'abord l'heure du fuseau horaire local en heure du fuseau horaire UTC, puis convertissez l'heure du fuseau horaire UTC en une valeur en millisecondes au format INT (à l'aide de la fonction UNIX_TIMESTAMP), puis stockez-la dans la base de données.
2. Lors de la lecture des données d'horodatage, convertissez d'abord la valeur en millisecondes au format INT en heure de fuseau horaire UTC (à l'aide de la fonction FROM_UNIXTIME), puis convertissez-la en heure de fuseau horaire local et enfin renvoyez-la au client.

Dans MySQL 5.6.4 et versions ultérieures, les données de type horodatage peuvent être stockées avec la plus grande précision en microsecondes (millionièmes de seconde). Le type de données est défini comme timestamp(N). La plage de valeurs de N est comprise entre 0 et 6. La valeur par défaut est 0. S'il doit être précis en millisecondes, définissez-le sur Timestamp(3). S'il doit être précis en microsecondes, définissez-le sur timestamp(6). son espace de stockage interne, mais le type d'horodatage reste inchangé. La plage de valeurs minimale et maximale de .

Définition du champ d'horodatage

La définition du champ d'horodatage affecte principalement deux types d'opérations :

• Lors de l'insertion d'un enregistrement, le champ d'horodatage contient DEFAULT CURRENT_TIMESTAMP. Si aucune donnée temporelle spécifique n'est spécifiée lors de l'insertion d'un enregistrement, le champ d'horodatage. La valeur sera définie. Lors de la mise à jour d'un enregistrement pour l'heure actuelle

• , le champ d'horodatage contient ON UPDATE CURRENT_TIMESTAMP Si aucune donnée temporelle spécifique n'est spécifiée lors de la mise à jour de l'enregistrement, la valeur du champ d'horodatage est définie sur l'heure actuelle

.

PS1 : CURRENT_TIMESTAMP signifie utiliser la fonction CURRENT_TIMESTAMP() pour obtenir l'heure actuelle, similaire à la fonction NOW()

Selon les deux types d'opérations ci-dessus, la colonne d'horodatage peut avoir quatre définitions de combinaison, leurs significations sont :

• Lorsque le champ est défini comme horodatage, cela signifie que le champ est dans Ni l'insertion ni la mise à jour ne sont automatiquement définies sur l'heure actuelle.

• Lorsqu'un champ est défini comme horodatage DEFAULT CURRENT_TIMESTAMP, cela signifie que le champ ne se verra attribuer l'heure actuelle que lorsqu'il sera inséré et qu'aucune valeur n'est spécifiée, et ne sera pas modifié lors de la mise à jour et qu'aucune valeur n'est spécifiée.

• Lorsqu'un champ est défini comme horodatage ON UPDATE CURRENT_TIMESTAMP, cela signifie que le champ se voit attribuer la valeur "0000-00-00 00:00:00" lorsqu'il est inséré et qu'aucune valeur n'est spécifiée et mis à jour à l'heure actuelle. une fois mis à jour et qu'aucune valeur n'est spécifiée.

• Lorsque le champ est défini comme horodatage DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, cela signifie que le champ ne spécifie pas de valeur lors de l'insertion ou de la mise à jour, et l'heure actuelle lui est attribuée.

PS1 : Il y aura des différences entre l'instruction de création de table exécutée dans MySQL et l'instruction de création de table finale. Il est recommandé d'utiliser SHOW CREATE TABLE TB_XXX pour obtenir l'instruction de création de table de la table créée.

Différences dans l'utilisation des champs d'horodatage dans différentes versions de MySQL

• Dans MySQL 5.5 et les versions précédentes, seuls DEFUALT CURRENT_TIMESTAMP ou ON UPDATE CURRENT_TIMESTAMP peuvent être définis pour un champ d'horodatage, mais cela a été annulé dans MySQL 5.6 et MySQL 5.7. Cette limitation :

• Dans la version MySQL 5.6, la valeur par défaut du paramètre explicit_defaults_for_timestamp est 1, dans la version MySQL 5.7, la valeur par défaut du paramètre explicit_defaults_for_timestamp est 0 ; Le type d'horodatage est par défaut NOT NULL. Dans la version MySQL 5.6, le type d'horodatage est par défaut NULL ; ON UPDATE CURRENT_TIMESTAMP;

Équivalent à l'horodatage c1 NULL DEFAULT NULL dans MySQL 5.6;

• Équivalent à l'horodatage c1 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIM ESTAMP dans MySQL 5.7 ;

• Lorsque l'horodatage c1 est par défaut 0 est utilisé dans l'instruction de création de table,

• Équivalent à l'horodatage c1 NOT NULL DEFAULT ‘0000-00-00 00:00:00’ dans MySQL 5.5

Équivalent à l'horodatage c1 NULL DEFAULT « 00 -00-00 00 dans MySQL 5.6 : 00:00 ; valeur par défaut du paramètre explicit_defaults_for_timestamp.

PS2 : lorsque la valeur par défaut de la colonne d'horodatage est "0000-00-00 00:00:00", l'utilisation de la valeur par défaut "pas dans la plage de valeurs d'horodatage" ne générera pas d'avertissement.

• Exception causée par le type d'horodatage

  Lorsque le paramètre MySQL time_zone=system, l'interrogation du champ d'horodatage appellera le fuseau horaire du système pour la conversion du fuseau horaire. Cependant, en raison du problème de verrouillage global dans le fuseau horaire du système, des contextes de thread fréquents se produiront. lorsque plusieurs accès simultanés à des données volumineuses se produisent, l'utilisation du processeur monte en flèche, la réponse du système ralentit et l'animation est suspendue.

  Type d'horodatage et sélection du type d'heure

  Dans certains documents "Guide de base de données", il est recommandé d'utiliser le type d'horodatage au lieu du champ datetime. La raison est que le type d'horodatage utilise 4 octets, tandis que le champ datetime en utilise 8. octets, mais avec des performances de disque améliorées et des coûts de mémoire réduits. Dans les environnements de production réels, l'utilisation du type d'horodatage n'apportera pas beaucoup d'amélioration des performances. Au lieu de cela, la définition et la plage de valeurs du type d'horodatage peuvent limiter et affecter l'utilisation professionnelle.

  Dans MySQL 5.6.4 et les versions ultérieures, les données de type d'horodatage (timestamp) peuvent être converties en microsecondes de la plus haute précision, et les données de type d'heure (datetime) peuvent également être converties en microsecondes de la plus haute précision. ) peut également obtenir le type d'horodatage. Le même effet, comme définir le champ comme dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); La plage d'accès du type d'heure (datetime) est ’1000; -01-01 00:00:00.000000&rsquo ; à "9999-12-31 23:59:59.999999", ce qui permet de mieux stocker les données de chaque période.

  Recommandations pour l'utilisation des types d'horodatage

  Lorsque vous vous souciez uniquement de l'heure de la dernière mise à jour des données, il est recommandé de définir la colonne d'horodatage comme TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

  Lorsque vous vous souciez de l'heure de création et heure de mise à jour, il est recommandé de définir l'heure de mise à jour comme champ d'horodatage, de définir l'heure de création comme DAETIME ou TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’ et de spécifier explicitement l'heure de création lors de l'insertion d'enregistrements

   ; Il est recommandé de définir uniquement une seule colonne d'horodatage et de définir explicitement les attributs DEFAULT et ON UPDATE

  Bien que le champ d'horodatage puisse être attribué ou mis à jour dans MySQL, il est recommandé d'insérer et de mettre à jour explicitement la colonne d'horodatage uniquement lorsque cela est nécessaire ;

  Il est recommandé de définir le paramètre time_zone sur une valeur en dehors du système. Par exemple, le serveur en Chine est défini sur "+8:00" ;
  Il est recommandé de conserver la version de test hors ligne de MySQL et la version de production en ligne ; cohérent.
  Similarités et différences entre Timestamp et datetime
  Mêmes points :
  • peut être automatiquement mis à jour et initialisé, et le format d'affichage par défaut est le même AAAA-MM-jj HH:mm:ss

  Différences :
  • horodatage La plage est la suivante : ‘1970-01-01 00:00:01’ UTC à ‘2038-01-19 03:14:07’ stockage en octets

  • La plage horaire de datetime : ‘1000-01-01 00:00:00’ à ‘9999-12-31 23:59:59’ ne prend pas en charge le fuseau horaire, le stockage sur 8 octets

  Définir l'horodatage et la date Heure de mise à jour automatique
  Lorsqu'une certaine donnée est mise à jour ou qu'une nouvelle donnée est insérée sans attribuer de valeurs à date et mydate, les deux champs date et mydate prendront automatiquement par défaut la valeur actuelle time

  CREATE TABLE `mytime` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `mydate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

  2038 question
  Quand Si l'heure stockée de l'horodatage est supérieure à « 2038-01-19 03:14:07 » UTC, mysql signalera une erreur, car il s'agit d'un problème de mysql lui-même, ce qui signifie que l'horodatage a une limite supérieure. Si elle dépasse, une erreur sera naturellement signalée. Certaines captures d'écran sont les suivantes :

  

  Solution
  horodatage Bien qu'il existe une limite supérieure, l'horodatage est enregistré, vous n'avez donc pas besoin de prendre en compte les problèmes de fuseau horaire si vous devez gérer les exigences liées au fuseau horaire. , vous pouvez résoudre la limite de 2038 , il est recommandé de changer l'horodatage en un type entier pour enregistrer l'horodatage puis le convertir dans le programme (cette solution n'a pas été implémentée, c'est juste une suggestion, utilisez-la avec prudence ! !)

  Si vous n'avez pas besoin de prendre en compte les problèmes de fuseau horaire, remplacez-le directement par l'horodatage de type datatime, car la plage de valeurs de datatime est beaucoup plus grande, vous pouvez voir l'image ci-dessus
  

  Idées de remplacement :
  1 ; . Modifiez le nom du champ d'origine ;

  ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';

  Créez un nouveau champ de type datatime (créez une nouvelle colonne pour remplacer l'original)

  ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';

  Copiez les données de la colonne du champ d'origine dans la nouvelle colonne de champ ; colonne d'origine ;

  UPDATE `student` SET `entry_date` = `temp_entry_date`;

  Le sql complet est le suivant : (Il convient de noter que la valeur par défaut de l'horodatage d'origine est également requise. De plus)

  ALTER TABLE `student` DROP `temp_entry_date`;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!