I'm adding the missing details in my own answer (special thanks for Ignacio, andig, Martin and Markus Laire for helping me to put the pieces of this puzzle together).
There are two problems to this question :
- Encoding conversion from UTF-8 to ASCII
- MySQL index limit to 767 bytes without enabling
innodb_large_prefix
for MySQL < 5.7.7 (looks like this is now enabled by default).
Answer for "Encoding conversion from UTF-8 to ASCII"
ASCII is a subset of UTF-8 so not all characters can be converted. ASCII only uses 128 characters (the first 128) per byte while UTF-8 bytes can use more. The ideal solution would be to use an encoding that support all 256 possibilities per 8-bits bytes. Some encoding like cp1252 supports most characters but even if this is true, some characters are invisible which could end up causing issues.
For a true byte by byte conversion the only reliable option is to use binary. for our user case given we use MySQL, the best option would be to have a VARBINARY(254)
(binary fields don't have encoding). After that it would be easy to simply:
INSERT into user_table set email_address='