WL#882: UNIQUE CONSTRAINT for BLOB
Affects: Server-7.1 — Status: Un-Assigned
RATIONALE About 20 users in BUG#4541 wanted this feature. SUMMARY Add support for unique blob/text checking. This should be done this way: Use the following (already existing) key definition syntax: CREATE TABLE ( ..., UNIQUE index_name (col1, col2, ...) USING HASH) where any of the columns could be a blob. Note that this optimization is also very useful for CHAR/VARCHAR columns as the generated hash index is much smaller than if we would save the whole key. It will also allow the user to do constraint on things that are longer than MyISAM MAX_KEY_LENGTH. In this case we should not create a key for 'index_name' but instead a unique check, if the handler support this. (If the handler doesn't support this, we should create a normal key for this, if possible.) From the user's point of view, the main differences between UNIQUE and UNIQUE ... USING HASH are: - UNIQUE ... USING HASH should take much less disk space. - With an UNIQUE ... USING HASH you can only use the index when searching on the whole key (no prefix key searches) and only with =. NDB can use UNIQUE ... USING HASH syntax to only create a HASH key on the index instead of an ordered index. In this case, any keys that contains NULL will be ignored. Part of this task is to do: - Check that this works properly with keys that have NULL in them. (When checking for duplicates keys with NULL should be regarded as different) - Get this to work with ALTER TABLE and CREATE TABLE - SHOW CREATE TABLE should show the USING HASH keyword. - There is not much that has to be changed on the MySQL side to support this but one has to do some changes to MyISAM to enable one to call mi_rkey() on a hash key. - We need to add a handler specific flag per key to inform the optimzier if the handler will stores null in the hash key or not. If not, we have to use table scans if the users has (or can have) a NULL value in the key. Some additional information about MyISAM: - The unique handling code (that is now mainly used for things like SELECT DISTINCT when we want to make the whole row unique) is in myisam/mi_unique,c MyISAM can already create unique constraints, with the 'MI_UNIQUEDEF *' parameter to mi_create()'. don't forget to close BUG#4541 when this task is implemented It's undecided whether foreign keys can reference unique text/blob columns.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.