MySQL - Make an existing field unique without deleting empty entries

Question

ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

This will make a filed a unique index but the command deletes all rows with an empty value, is there any way I can avoid that happening. The field may have NULL values if that helps.

Answer 1

Set the "empty" (?) values to NULL and don't use IGNORE. The purpose of ALTER IGNORE is to remove duplicate values. Multiple NULL are not duplicates of each other, since the expression NULL = NULL is not true (and is also not false).