Details
-
Type:
Task
-
Status: Closed
-
Priority:
Major
-
Resolution: Duplicate
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
From the MySQL Manual:
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
Can you please implement a way to specify that uniqueness should extend beyond the prefix and apply to the entire column? Obviously the index could be used as a start, but any rows with matching prefix should be compared against the entire value.
1) Search the index
2) Compare the VARCHAR length of any matching prefix columns
3) Compare the actual values
Sure it would be a bit slower than index-only, but grant us the flexibility to decide full comparisons v.s. index and remove this limitation.
Some practical examples:
SOF - Best primary key for storing URLs
SOF - Most efficient way to store URLs in Mysql?
SE - MySQL: Storing unique URLs
Thanks!
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.11, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character.
So it is not so simple to say that "You can have an index over the whole VARCHAR column." Again, I refer to the examples I posted. Uniqueness cannot be applied to long URLs in VARCHAR fields because of this limitation. I would simply suggest that the implementation for enforcing uniqueness be definite. The solution that I posted could be one way. Use a prefix index for a given storage engine and when collisions do occur in the index, do a serial search for a literal match.
You're right. The limitation of 767 bytes applies to indexes over VARCHAR too.
But as I said above, MDEV-371 will remove this limitation, one will be able to have UNIQUE indexes over arbitrary long sets of columns.
You can have an index over the whole VARCHAR column if you don't specify the prefix length.
For BLOB/TEXT columns you must specify the prefix that's true. Removing this limitation is MDEV-371