Can you specify the table collation when creating a table via db_create_table()
(or hook_schema()
)? I didn't find any documentation (or issue) on this, but I hope it is possible? Otherwise, this is definitely a feature request for D8.
Comment | File | Size | Author |
---|---|---|---|
#7 | drupal-1144644-7-enabling-myqsl-collation.patch | 2.93 KB | BarisW |
Comments
Comment #1
drunken monkeyI'll take that as a "no".
Feature request: While the "general_ci" collation makes sense in most cases, there are some when this leads to unexpected, and unwanted, results. E.g., when using primary / unique keys of type varchar, "A" and "à" (and lots of others) will be treated equivalently, resulting in possible collissions and, consequently, exceptions. If a module developer knows that this might happen for a module, they should be able to specify another collation to circumvent this (utf8_bin, probably).
A "collation" key in the schema structure should do. Alternatively, I'd also be content with a "binary" key (defaulting to
FALSE
) which switches to binary collation (utf8_bin) when set.Comment #2
fabsor CreditAttribution: fabsor commentedI agree that this could be an issue, however, you can set a global setting in settings.php, which affects all tables. Which setting would take precedence upon installation?
Comment #3
drunken monkeyYes, I know, but that doesn't solve the issue. We want to be able to choose the collation from the module, not depend on the user to change that setting. For most tables, the default setting is the better choice after all.
Comment #4
Sylvain Lecoy CreditAttribution: Sylvain Lecoy commentedWe should add a collation field in a column definition:
Comment #5
jefkin CreditAttribution: jefkin commenteddrunken monkey ++
Sylvain Lecoy ++
Nice expression of the needed configuration options Sylvain
Comment #6
BarisW CreditAttribution: BarisW commentedDeleted my comment, thought this was a Search API DB issue, but it seemed a Drupal Core thing.
Comment #7
BarisW CreditAttribution: BarisW commentedPatch attached to add this in D8. Allows setting collation on a table-base or on a column-base.
Comment #8
PanchoNice, but what about performance?
Crell says that joining tables with different collations leads to indexes being ignored and thus incredibly slow queries, see #772678-47: Database default collation is not respected.
Does this also apply to differing collations within a table? We should find out.
Comment #9
michlis CreditAttribution: michlis commentedPatch applies to latest D8 version, testing in progress.
Comment #12
marvil07 CreditAttribution: marvil07 as a volunteer commentedAdding related issue: binary collation in mysql can be specified now after #1237252.
It has been a while since this patch was written, it does not apply anymore, moving to NW, also I am wondering about how to test this.