[Phpmyadmin-devel] BINARY and VARBINARY

[Marc Delisle]

Alexander M. Turek wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi devels,
> 
> it looks as if MySQL has silently deprecated the BINARY sttribute in
> MySQL 4.1. If you create a CHAR(3) BINARY field, it becomes CHAR(3)
> CHARACTER SET latin1 COLLATE latin1_bin.

At least they give this explanation:
-----
As of MySQL 4.1, values in CHAR and VARCHAR columns are sorted and 
compared according to the collation of the character set assigned to the 
column. Before MySQL 4.1, sorting and comparison are based on the 
collation of the server character set; you can declare the column with 
the BINARY attribute to cause sorting and comparison to be case 
sensitive using the underlying character code values rather then a 
lexical ordering. BINARY doesn't affect how the column is stored or 
retrieved.
----------

Because BINARY did not affect how the column was stored or retrieved,
they dropped it, because it's purpose (sorting and comparison) is now
done with the collation feature.

Marc


> 
> Old [VAR]CHAR BINARY fields are mapped to [VAR]BINARY. This leads us to
> various incompatibilities:
> 
> 1) We don't have these fields in our list of possible table fields and I
> don't know if MySQL 3.23.32 supports the syntax, so adding them
> generally wouldn't we a good idea, imho.
> 
> 2) tbl_properties_structure.php recognizes these fields correctly as
> binary fields, but strips off the string "BINARY", so "VARBINARY(20)"
> becomes "var(20)" and even worse "BINARY(20)" becomes "(20)".
> 
> 3) when trying to alter these fields, both are recognized as VARCHAR
> BINARY, which is dangerous because if we just want to change the size
> for instance, they get changed to "VARCHAR CHARACTER SET latin1 COLLATE
> latin1_bin"...
> 
> 3) The binary column in tbl_properties_structure is superfluous, imho.
> And so is the BINARY entry in the list of field attributes.
> 
> 4) The parser does not recognize BINARY as column type.
> 
> Here's a simple table for reproducing the problem:
> 
> CREATE TABLE `binarytest` (
>   `one` binary(3),
>   `two` varbinary(10)
> )
> 
> - --
> 
> Alexander M. Turek
> <rabus at users.sourceforge.net>
> 
>        _           __  __          _       _           _
>  _ __ | |__  _ __ |  \/  |_   _   / \   __| |_ __ ___ (_)_ __
> | '_ \| '_ \| '_ \| |\/| | | | | / _ \ / _` | '_ ` _ \| | '_ \
> | |_) | | | | |_) | |  | | |_| |/ ___ \ (_| | | | | | | | | | |
> | .__/|_| |_| .__/|_|  |_|\__, /_/   \_\__,_|_| |_| |_|_|_| |_|
> |_|         |_|           |___/
>                                     <http://www.phpmyadmin.net>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.4 (GNU/Linux)
> 
> iD8DBQFBC+XHoHdnxuobFY4RAl5MAJ9rhasmTe78CKqEPzqnXC1NjSdtnACfQaiO
> C5a5uEpLgo0zrS/QBSxGH1k=
> =IzsE
> -----END PGP SIGNATURE-----
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by OSTG. Have you noticed the changes on
> Linux.com, ITManagersJournal and NewsForge in the past few weeks? Now,
> one more big change to announce. We are now OSTG- Open Source Technology
> Group. Come see the changes on the new OSTG site. www.ostg.com
> _______________________________________________
> Phpmyadmin-devel mailing list
> Phpmyadmin-devel at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
>