Stewart Smith wrote: > On Wed, Aug 13, 2008 at 10:57:58PM -0700, Monty Taylor wrote: >> On the other hand, if there was a per-server collation set by locale, >> would that be sufficient? > > What about per-index? > > Thinking about presentation of a data set for different locales. e.g. I > want it sorted the way i expect it, but somebody viewing in $language > probably wants it in their collation. > > Is this a sensible idea or am I just completely missing the issues?
Actually, I think is one of the most sensible ideas regarding collation that I have heard up until now. Basically, if I set a collation on something, it's because I want to sort (ORDER BY) or compare (WHERE) values in a column based on a specific locale. Therefore, I really don't think storing a per-column collation is very useful at all. By the same token, I think being able to declare a collation for an index is very useful. Something like: CREATE TABLE t1 ( a CHAR(100) NOT NULL , INDEX (a) COLLATE utf_bin_cs ); Which would remove the need to store per-column collation. The user could still override the collation for sorts and searches in the query or session, like so: SELECT a FROM t1 ORDER BY a COLLATE utf_bin_ci; or SELECT a FROM t1 WHERE a >= 'aB' COLLATE utf_bin_ci; The index would be stored on disk in the collation identified in the table definition, inheriting a default collation from the schema. If the requested search/sort uses a collation that is not different from the stored index collation for that column, then the index is used as-is for retrieval. If the requested collation is different, *or no index on the column is available*, then either the records are retrieved via an alternate index or a sequential data scan, and then the sort or compare is done in a temporary space using the alternate collation comparison callback. The above would indicate that the only necessary collation metadata to store would be *per schema* and *per index*. This, IMHO, would simplify things considerably, as per-table and per-column collations would not be necessary... Thoughts? -jay _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
