Propel and binary strings in MySQL

Question

I want to differ between characters c and č or e and é.

Is it possible to handle binary strings for mysql with Propel? Or can I handle it without Propel directly in my MySQL-database (only for one table, some other tables still shall treat it all the same). However, queries will still be executed with Propel (does Propel support BINARY in queries?).

Propel VARBINARY only maps to MySQL BLOB (which is the same as Propel BLOB) and Propel's VARBINARY and BLOB seem pretty complicated to use (they return a resource id).

So I tried Propel CLOB, which maps to MySQL LONGTEXT. This works well in usage, but then again e and é are the same. So it is not different from my current VARCHAR.

Currently I am using VARCHAR with a length of 50.

heh... I can make it an answer if you want. It's probably a dupe of something as well I'm sure — Dagg Nabbit
– Dagg Nabbit, Commented Mar 4, 2012 at 15:12
Feel free to post an answer, I already tried to collect what I was able to see so far. Hope it is not total nonsense ;) — aufziehvogel
– aufziehvogel, Commented Mar 4, 2012 at 15:14
That looks better than what I was going to write. Mark it right ;) (except I think you might be a little confused about binary. Try not to store binary objects in your DB if you can avoid it. utf8 should be fine, I'm not sure what utf8_bin is.) — Dagg Nabbit
– Dagg Nabbit, Commented Mar 4, 2012 at 15:16

aufziehvogel · Accepted Answer · 2012-03-04 15:24:29Z

General about collation

Using MySQL collation, you can specify how different characters stored in the column should be compared. To get a better overview on how each collation treats characters, you can look on collation-charts.org.

The binary collations will treat each characters differently, so characters like e and é are not the same.

To understand better how collations work, you could also have a look on the Turkish UTF-8 collation of MySQL. There you will see that ğ and g which are separate characters in Turkish are treated as different ones, whereas others are deemed the same.

Collations do not only specify which characters are the same, but also in which order they are sorted when you sort after a column.

Your choice in this case will be utf8_bin.

Usage of collations in Propel

To set the whole stuff in Propel you can use a <vendor> tag within your schema.xml:

<column name="tag" phpName="Tag" type="VARCHAR" size="50" required="true">
  <vendor type="mysql">
    <parameter name="Collate" value="utf8_bin"/>
  </vendor>
</column>

Collectives™ on Stack Overflow

Propel and binary strings in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related