Bug #76372 The "BINARY" charset attribute is not documented for CHAR, VARCHAR etc
Submitted: 18 Mar 2015 9:51 Modified: 20 Mar 2015 15:08
Reporter: Gleb Shchepa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[18 Mar 2015 9:51] Gleb Shchepa
Description:
In "CREATE TABLE Syntax" the "data_type" non-terminal is documented as:

data_type:
...
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
...
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
...

By the implementation, CHAR[ACTER] and VARCHAR types also support the "BINARY" attribute like TEXT etc types, but this is not documented.

Note: there is also NCHAR/NATIONAL CHAR[ACTER] type that seems to support the "BINARY" attribute, but they are missed in the CREATE TABLE syntax documentation.

How to repeat:
root@localhost [test] > CREATE TABLE t1 (c1 CHAR(1) BINARY, c2 VARCHAR(1) BINARY, c3 NCHAR(1) BINARY);                               Query OK, 0 rows affected (0,01 sec)

root@localhost [test] > SHOW CREATE TABLE t1;                                                                                        +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` char(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `c2` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `c3` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
[19 Mar 2015 13:32] Paul DuBois
re: "Note: there is also NCHAR/NATIONAL CHAR[ACTER] type that seems to support the "BINARY" attribute, but they are missed in the CREATE TABLE syntax documentation."

Those are lesser-used synonyms documented in the data types chapter. We don't clutter the CREATE TABLE syntax with all the data type synonyms.
[19 Mar 2015 13:33] Paul DuBois
Gleb, how would you modify the syntax to include the BINARY attribute where you indicate? Something like this?

[CHARACTER SET charset_name | BINARY]
[20 Mar 2015 15:08] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.