Mysql EXCEPT unique index empty or empty

Question

I have a MySQL MyISAM table with ISBN. I want to create a UNIQUE index that will not throw a "duplicate" error if the value is empty or empty.

Is it possible?

Answer 1

Since you are using the MyISAM (or INNODB) storage engine, the short answer is no. A few empty lines will not work for a unique index constraint, at least a few zeros. Best of all, if you want to use only the SQL solution, it is to create a regular index in the ISBN field, and then use the stored procedure to work as a proxy proxy server that checks if it is unique, if not empty or empty.

If only zeros work for you, here is a basic creation:

CREATE TABLE `books` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `isbn` int(13) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `isbn` (`isbn`) ) 

Just a note ... Remember that the value "13" specified for int is only the number of digits that will be displayed when returning in the request, not the size of an integer.

Answer 2

you can use something like this,

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

This statement creates an index for which the values ​​must be unique (with the exception of NULL values, which may appear several times). Consult MySQL INDEXES .