ALTER TABLE sujet ADD tmpswap DATETIME;
UPDATE sujet SET tmpswap = FROM_UNIXTIME(ancien);
ALTER TABLE sujet DROP ancien;
ALTER TABLE sujet CHANGE tmpswap ancien DATETIME;

(sujet : nom de la table ; ancien : nom de la colonne initialement en (big)int)

ALTER TABLE sujet ADD tmpswap DATE;
UPDATE sujet SET tmpswap = STR_TO_DATE(ancien, '%d/%m/%Y');
ALTER TABLE sujet DROP ancien;
ALTER TABLE sujet CHANGE tmpswap ancien DATE;

(sujet : nom de la table ; ancien : nom de la colonne initialement en varchar)

Pourquoi utiliser le type DATE ?

• pour simplifier toute manipulation sur les dates : vous pouvez ainsi nativement utiliser toutes les fonctions date du SGBD sans avoir à d'abord reconvertir [le format de] la date
• le format interne (us) prévu permet nativement un tri (lexicographique) correct
• le formatage au format fr (ou autres) ne justifie pas une colonne en VARCHAR : tous les langages ou même les SGBD (DATE_FORMAT pour MySQL) permettent facilement la conversion. Et quid du jour où votre site devra être internationalisé ?
• le SGBD s'assure que la date est un minimum correct, toute erreur à l'insertion/modification est minimisée. Au contraire, avec un varchar, si le format est incorrect, ça peut être catastrophique suivant l'importance des données. MySQL avec un sql_mode comprenant les valeurs NO_ZERO_IN_DATE (interdit des composantes de date à 0) et NO_ZERO_DATE (interdit les dates "zéro" 0000-00-00 obtenues par défaut dans des cas +/- foireux) et sans ALLOW_INVALID_DATES assurera une cohérence des plus strictes.

On peut aisément remplacer un CASE comme celui-ci :

SELECT CASE <in>
    WHEN 'FT' THEN 'Fiche Technique'
    WHEN 'PV' THEN 'Procès Verbal'
    WHEN 'NF' THEN 'Norme'
    ELSE 'Valeur par défaut'
END;

Par :

COALESCE(
    ELT(
        FIELD(<in>, 'FT', 'PV', 'NF'),
        'Fiche Technique', 'Procès Verbal', 'Norme'
    ),
    'valeur par défaut'
)

C'est l'"équivalent" PHP de :

Par contre : ça revient éventuellement à "réinventer" le type ENUM

SET FOREIGN_KEY_CHECKS=0;
 
-- les différentes opérations sql à exécuter en ignorant les clés étrangères
 
SET FOREIGN_KEY_CHECKS=1;

Sachez tout d'abord que MySQL dispose de deux types différents par rapport aux date/heure :

• TIMESTAMP
• DATETIME (comprend DATE et TIME)

Ils diffèrent par ce qu'ils impliquent :

• le type TIMESTAMP est surtout destiné à son initialisation automatique (INSERT) et à sa mise à jour automatique lorsqu'une modification est apportée à la ligne (UPDATE)
• le type DATETIME est bien moins limité (de '1000-01-01 00:00:00' à '9999-12-31 23:59:59') que TIMESTAMP (de '1970-01-01 00:00:01' UTC à '2038-01-19 03:14:07' UTC)
• le format TIMESTAMP est convertie de la timezone MySQL courante vers UTC à l'insertion et, inversement, de UTC à la timezone en cours, au SELECT. À l'inverse, le format DATETIME est figé : il correspond à une date/heure pour une timezone donnée (vue qu'elle n'est pas associée à la date, cette information est perdue par la suite au niveau de MySQL) : le DATETIME restera tel qu'il est, à l'INSERT comme au SELECT, il n'y a aucune conversion interne d'une timezone à une autre (UTC). Cependant, notez qu'au besoin, vous pouvez faire vous-mêmes les conversions de timezone via la fonction CONVERT_TZ qu'intègre MySQL.

Après cette brève présentation des types date/heure, il est possible de modifier à la volée la timezone courante au lieu d'hériter de celle par défaut (appelée SYSTEM). Pour ce faire, il faut modifier au niveau session la variable nommée … time_zone par une commande SET. Mais, problème, par défaut, vous ne pouvez assigner à cette variable qu'un décalage +/-heure:minute (exemple set time_zone = '+2:00';) et non utiliser directement des noms comme Europe/Paris à moins d'importer les timezones au sein de MySQL. Autant dire que c'est gênant pour deux raisons :

• les importer prend peu de temps et de connaissances mais encore faut-il avoir les accès requis (en clair, sur du mutualisé, c'est tout bonnement impossible)
• pour travailler sur les timezones au niveau de MySQL, c'est obligatoire. Comment, sinon, gérez les heures d'été et d'hiver ? Si ces dates sont importantes et pour beaucoup de trafic, vous vous connectez dans la nuit de ces changements d'heures pour apporter la modification dans vos sources ? Non, c'est ingérable.

Donc, pour importer ces fameuses timezones :

• sous Unixoïdes :
  1. Importer les données de votre propre système en entrant la commande : mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
  2. Relancer MySQL
• sous Windows :
  1. Arrêter le serveur MySQL s'il est lancé
  2. Se rendre sur http://dev.mysql.com/downloads/timezones.html
  3. Télécharger le fichier qui correspond au lien POSIX standard Time zone description tables, version 20XXn
  4. Dézipper l'archive obtenue dans le sous-répertoire mysql où sont entreposées vos bases de données (voir la valeur de la directive datadir de votre fichier my.ini - ces tables existent déjà, écrasez-les après les avoir préalablement copiées, sait-on jamais)
  5. Redémarrer MySQL

Tester que tout fonctionne :

mysql> set time_zone = 'Europe/London';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-07-26 15:57:20 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'Europe/Paris';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-07-26 16:57:20 |
+---------------------+
1 row in set (0.00 sec)

Quant à ceux qui ne peuvent que subir, tout ce que vous pouvez faire, c'est remplacer vos NOW() et autres fonctions MySQL par celles de votre langage. Par exemple, pour PHP, qui embarque toutes ces informations de fuseaux horaire, il suffira de faire un préalable date_default_timezone_set('Europe/Paris'); en début de code et de remplacer les NOW() de vos requêtes par date('Y-m-d H:i:s') ou autre DateTime.

Si vous avez un élément "unique" et qu'il est identifié comme tel au niveau de MySQL par une clé primaire ou une contrainte unique (UNIQUE KEY) alors vous pouvez utiliser une requête de type INSERT IGNORE.

Pour l'exemple, prenons le cas d'une table d'utilisateurs minimaliste, avec id (clé primaire) plus login qui doit être unique :

Comment s'assurer à l'inscription d'un nouvel utilisateur que le login n'est pas déjà pris par quelqu'un d'autre ? C'est simple, vous tentez de faire silencieusement l'insertion et vous regardez ensuite si une ligne a été insérée ou non (mysql(i) : mysql(i)_affected_rows, pdo : PDO::rowCount) :

(le code est en PHP mais le principe peut être adapté à tout langage)

IGNORE n'est qu'une option pour que lorsque l'on tente d'insérer un "doublon", la requête n'échoue pas. Elle est destinée à considérer qu'une tentative d'insertion de doublon est un cas prévu par notre programme et non une erreur (vous en obtiendriez une sans IGNORE).

Par contre, cette solution ne convient pas si vous avez plusieurs contraintes d'unicité et que vous voulez savoir laquelle a empêché l'insertion. En effet, si vous voulez notamment indiquer à l'utilisateur si c'est l'adresse email ou le login qui est déjà pris, vous devriez faire un SELECT normal par contrainte unique. Je dis "devriez" parce qu'il est éventuellement possible, pour un INSERT sans l'option IGNORE, de parser le message d'erreur où la colonne ou contrainte ou index empêchant l'insertion est mentionné, solution, qui, à mon sens, n'est pas très viable (et encore moins portable).

Comme ci-dessus, si chaque ligne peut être identifiée de manière unique par la valeur d'une ou plusieurs colonnes, définies comme clé primaire (PRIMARY KEY) ou comme clé unique (UNIQUE KEY), alors il est possible de réaliser cette opération insertion sinon mise à jour en une seule requête de type INSERT … ON DUPLICATE KEY UPDATE au lieu d'avoir à recourir à cet "algo" traditionnel :

Pour illustrer, prenons une situation courante, réaliser un import, ici de joueurs, via un flux XML fourni par la fédération. Tout d'abord notre table, volontairement sommaire, joueurs :

On reconnaît un joueur d'un autre via son numéro de licence, unique. Le but, en parsant ce flux, est double :

1. ajouter les nouveaux joueurs à notre base
2. mettre à jour éventuellement les informations personnelles de ceux que nous avions (changement d'adresse, classement, etc)
3. (on ne gère pas les retraits de licence - DELETE)

Encore une fois, c'est codé en PHP mais parfaitement transposable à tout langage puisque c'est la requête SQL qui compte.