In MySQL UNIQUE equivale ad un indice sulla colonna.
Nel caso si voglia cancellare la proprietà UNIQUE si può usare DROP INDEX.
DROP INDEX nome_indice ON tabella
Il “nome_indice” coincide spesso con il nome della colonna (a parte la chiave primaria che si chiama “PRIMARY”). Comunque, in caso di dubbi:
SHOW INDEX FROM tabella
Mi sono sempre chiesto se LIMIT tagliasse i dati “a monte” della query o “a valle”…
Un tempo mettevo LIMIT convinto che servisse, poi mi sono accorto “a naso” che non pareva proprio così…
Una discussione in giro per irc ha chiarito il punto.
Temevo che fosse come in effetti e’: LIMIT taglia i risultati dopo che la query è stata eseguita e i record trovati sono molto più di quelli che vengono restituiti. (cit. datacharmer).
web:Agenda> SELECT SQL_CALC_FOUND_ROWS * FROM folks LIMIT 1; +----+------+----------+------------+--------+---------------------+---------------------+--------+ | id | name | surname | birth | gender | created | updated | active | +----+------+----------+------------+--------+---------------------+---------------------+--------+ | 1 | xxxx | xxxxxxxx | 1973-06-02 | m | 2010-02-11 20:11:00 | 2010-02-13 17:33:04 | 1 | +----+------+----------+------------+--------+---------------------+---------------------+--------+ 1 row in set (0.00 sec) web:Agenda> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 101 | +--------------+ 1 row in set (0.00 sec) web:Agenda> SELECT SQL_CALC_FOUND_ROWS * FROM folks WHERE id=1; +----+------+----------+------------+--------+---------------------+---------------------+--------+ | id | name | surname | birth | gender | created | updated | active | +----+------+----------+------------+--------+---------------------+---------------------+--------+ | 1 | xxxx | xxxxxxxx | 1973-06-02 | m | 2010-02-11 20:11:00 | 2010-02-13 17:33:04 | 1 | +----+------+----------+------------+--------+---------------------+---------------------+--------+ 1 row in set (0.01 sec) web:Agenda> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
Un WHERE ben piazzato (se si può mettere), è ben più efficace. Ooookei.
Avendo un database in mano mai visto ma progettato decentemente, può essere interessante cercare di valutare se ci sono campi utili per linkare le relazioni fra di loro (e valutare prima di una modifica quali relazioni dovrebbero essere toccate). (Spesso i campi per creare questi collegamenti sono dei field *_id che si ripetono nelle relazioni interessate.)
Ho avuto la necessità quindi di vedere quante e quali relazioni facessero uso di un determinato campo in un database (o, detta in soldoni, ho cercato di capire dove fossero presenti determinate colonne nelle tabelle del database).
Win. Almeno so dove guardare. 
Smanettando nei database può capitare di essere incuriositi da alcune relazioni dentro un database (quando si passa attraverso framework già preparati da qualcun’altro, può fare comodo capire cose a grandi linee).
In MySQL, i metadati dei database e delle tabelle sono nella tabella “di sistema” information_scheme.
Quante righe hanno le tabelle di quel database?
Se non vogliamo usare mysqlshow come spiegato in precedenza possiamo operare da dentro il server:
Risultato:
+------------+------------+ | table_name | table_rows | +------------+------------+ | folks | 95 | | info | 47 | | addresses | 31 | | phones | 11 | | emails | 2 | +------------+------------+ 5 rows in set (0.04 sec)
Nel mio caso specifico la query è stata utile per sapere quante tabelle in un framework potevo evitare di guardare (quelle con 0 righe).
Se nel delirio più totale si vogliono sapere quali utenti ci sono del db-server (ché non ce li si ricorda più dopo un po’)
e quali db hanno in mano… 
Altre cose interessanti si possono sicuramente trovare qua (Common MySQL query).
Come dicevo, la cosa può essere fatta anche con mysqlshow –count – che è talmente utile, da essere un alias a mysqlshow nel mio bashrc.
Un po’ di insiemistica con tabelle SQL.
Come faccio a sapere quali elementi sono in comune fra due relazioni?
Intersezione
Come faccio a sapere quali elementi non sono in comune fra due relazioni?
Minus
Con subquery:
Con join:
Dopo gli accidenti tirati precedentemente, ho scoperto che dare un comando CHANGE su una colonna dichiarandola come TIMESTAMP ha il seguente effetto: il default diventa CURRENT_TIMESTAMP e l’extra è ON UPDATE CURRENT_TIMESTAMP.
Avevo letto che CHANGE e MODIFY cancellavano gli attributi (l’Extra) delle colonne cui cambiavano il tipo, e così mi sono messo ad assegnare TIMESTAMP e DATETIME ai due campi created/updated in modo da togliermi i dubbi.
(dal man: “When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.“)
sim:test> ALTER TABLE A CHANGE created created DATETIME; sim:test> ALTER TABLE A CHANGE updated updated TIMESTAMP; [...] | created | datetime | YES | | NULL | | | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
Alla fine mi sono un po’ capito leggendo qua.
I dati di tipo timestamp dovrebbero essere dei datetime (e lo sono come formato), ma a differenza di questi ultimi si aggiornano con l’ora attuale.
The TIMESTAMP data type offers automatic initialization and updating.
Dopo aver reso la colonna updated CURRENT_TIMESTAMP con aggiornamento sull’UPDATE dei dati, ho potuto fare:
ALTER TABLE A MODIFY created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
e inserire un paio di dati:
sim:test> INSERT INTO A (word) VALUES ('gastone');
sim:test> INSERT INTO A (word,created) VALUES ('paperino',NULL);
col risultato:
| 5 | gastone | 0000-00-00 00:00:00 | 2010-02-11 23:42:03 | | 6 | paperino | 2010-02-11 23:42:23 | 2010-02-11 23:42:23 |
Sul man c’è scritto pure questo, ma a me continua a sembrare un comportamento strano.
TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp.
In conclusione, se volete avere dei campi con la creazione e l’aggiornamento automatico delle date incluso, leggetevi la pagina di Mysql sui timestamp.
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;