Qua c’è da uscire di senno. A me MySQL sta pure simpatico, ma fa veramente di tutto per farmi sentire male.
Parto proprio dall’inizio.
sim:none> CREATE DATABASE test CHARACTER SET = 'UTF8'; Query OK, 1 row affected (0.00 sec) sim:none> CONNECT test; Connection id: 230 Current database: test
Ammettiamo che io voglia creare una tabella canonica con vari campi, fra cui l’id come PK, un campo per segnare quando il record è stato creato (created) ed uno per segnarmi quando è stato aggiornato l’ultima volta (updated).
sim:test> CREATE TABLE A (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(8) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated TIMESTAMP NOT NULL );
Query OK, 0 rows affected (0.00 sec)
La situazione della mia tabella A per ora è chiara.
sim:test> SHOW CREATE TABLE A \G;
*************************** 1. row ***************************
Table: A
Create Table: CREATE TABLE `A` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`word` varchar(8) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Inserisco anche un paio di record che appaiono come ci si aspetta:
sim:test> INSERT INTO A (word) VALUES ('pippo'),('pluto');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
sim:test> SELECT * FROM A;
+----+-------+---------------------+---------------------+
| id | word | created | updated |
+----+-------+---------------------+---------------------+
| 1 | pippo | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
| 2 | pluto | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
+----+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
Nel caso si decida di aggiornare un record:
sim:test> UPDATE A SET word = 'topolino' WHERE id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 sim:test> SELECT * FROM A; +----+----------+---------------------+---------------------+ | id | word | created | updated | +----+----------+---------------------+---------------------+ | 1 | pippo | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 | | 2 | topolino | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
A questo punto decido che questa disposizione dei dati non mi va più bene, che fare? Semplice (* le ultime parole famose TM ), leggo il manuale e sistemo. Seee…
Un po’ di pazzia MySQL:
sim:test> SHOW COLUMNS FROM A; +---------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | word | varchar(8) | NO | | NULL | | | created | timestamp | NO | | CURRENT_TIMESTAMP | | | updated | timestamp | NO | | 0000-00-00 00:00:00 | | +---------+------------------+------+-----+---------------------+----------------+ 4 rows in set (0.00 sec) sim:test> ALTER TABLE A ALTER created DROP DEFAULT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 sim:test> SHOW COLUMNS FROM A; +---------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | word | varchar(8) | NO | | NULL | | | created | timestamp | NO | | NULL | | | updated | timestamp | NO | | 0000-00-00 00:00:00 | | +---------+------------------+------+-----+---------------------+----------------+ 4 rows in set (0.00 sec) sim:test> ALTER TABLE A CHANGE created creat TIMESTAMP; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 sim:test> SHOW COLUMNS FROM A; +---------+------------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------------------+-----------------------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | word | varchar(8) | NO | | NULL | | | creat | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | updated | timestamp | NO | | 0000-00-00 00:00:00 | | +---------+------------------+------+-----+---------------------+-----------------------------+ 4 rows in set (0.00 sec)
Con un magico DROP del DEFAULT di un campo ed un cambio nome ottengo di nuovo il CURRENT_TIMESTAMP come default, e pure l’update automatico sul campo.
Provare per credere:
sim:test> INSERT INTO A (word) VALUES ('minnie');
Query OK, 1 row affected (0.00 sec)
sim:test> SELECT * FROM A;
+----+----------+---------------------+---------------------+
| id | word | creat | updated |
+----+----------+---------------------+---------------------+
| 1 | pippo | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
| 2 | topolino | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
| 3 | minnie | 2010-02-11 23:02:36 | 0000-00-00 00:00:00 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
sim:test> UPDATE A SET word = 'clarabella' WHERE id=3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
sim:test> SELECT * FROM A;
+----+----------+---------------------+---------------------+
| id | word | creat | updated |
+----+----------+---------------------+---------------------+
| 1 | pippo | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
| 2 | topolino | 2010-02-11 22:23:41 | 0000-00-00 00:00:00 |
| 3 | clarabel | 2010-02-11 23:03:35 | 0000-00-00 00:00:00 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
Robe da pazzi.
Note:
1) MySQL aggiunge alle funzionalità dell’SQL (che vorrebbe una sola query per riga) la possibilità di comandi multipli in un solo statement: ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
Write a comment
You need tologin.