From: Jeremy Cole
Date: November 3 2007 5:07pm
Subject: Re: SHOW CREATE TABLE and auto_increment
List-Archive: http://lists.mysql.com/internals/35153
Message-Id: <472CAAD9.1040000@provenscaling.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Howdy,

This is an interesting case:

| pageview_data | CREATE TABLE `pageview_data` (
 > >>   `query` varchar(150) character set latin1 NOT NULL default '',
 > >>   `pageviews` int(11) default NULL,
 > >>   `date` varchar(10) character set latin1 default NULL,
 > >>   PRIMARY KEY  (`query`)
 > >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |

Notice above how it says "character set latin1" for query.  It prints 
that because it differs from the table's default.  When you specify 
"character set latin1" without a collation, the collation assumed is the 
default collation for that character set, not the default for the table.

Here's an interesting test case:

 >>>>>
mysql> create table cs_t (a char(10) character set latin1) default 
character set latin1 collate latin1_bin;
Query OK, 0 rows affected (0.65 sec)

mysql> show create table cs_t \G
*************************** 1. row ***************************
        Table: cs_t
Create Table: CREATE TABLE `cs_t` (
   `a` char(10) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.03 sec)

mysql> show full columns from cs_t \G
*************************** 1. row ***************************
      Field: a
       Type: char(10)
  Collation: latin1_swedish_ci
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
1 row in set (0.01 sec)
<<<<<

Compare to this:

 >>>>>
mysql> create table cs_t (a char(10)) default character set latin1 
collate latin1_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table cs_t \G
*************************** 1. row ***************************
        Table: cs_t
Create Table: CREATE TABLE `cs_t` (
   `a` char(10) collate latin1_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show full columns from cs_t \G
*************************** 1. row ***************************
      Field: a
       Type: char(10)
  Collation: latin1_bin
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
1 row in set (0.02 sec)
<<<<<

Interestingly in this case it still prints the "collate" bit for the 
column, which is redundant.

Regards,

Jeremy

Rick James wrote:
> | query     | varchar(150) | latin1_swedish_ci |      |  PRI |         |
>                              ^^^^^^^^^^^^^^^^^
> seems to disagree with
> 
> `query` varchar(150) character set latin1 NOT NULL default '',
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
>                                        ^^^^^^^^^^^^^^^^^^
> 
>> -----Original Message-----
>> From: Stewart Smith [mailto:stewart@stripped] 
>> Sent: Thursday, November 01, 2007 8:43 PM
>> To: Rick James
>> Cc: Kathryn Steinbrink; internals@stripped
>> Subject: RE: SHOW CREATE TABLE and auto_increment
>>
>> On Fri, 2007-10-26 at 09:51 -0700, Rick James wrote:
>>> Why does it fail to spell out collation correctly?
>> What exactly is incorrect?
>>
>>>>>> mysql> show create table pageview_data;
>>>>>> ...
>>>>>> | pageview_data | CREATE TABLE `pageview_data` (
>>>>>>   `query` varchar(150) character set latin1 NOT NULL 
>> default '',
>>>>>>   `pageviews` int(11) default NULL,
>>>>>>   `date` varchar(10) character set latin1 default NULL,
>>>>>>   PRIMARY KEY  (`query`)
>>>>>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
>>> mysql> SHOW FULL COLUMNS FROM pageview_data;
>>>  
>>>
>> +-----------+--------------+-------------------+------+-----+-
>> --------+-
>>> ------+------------+---------+
>>> | Field     | Type         | Collation         | Null |  
>> Key | Default |
>>> Extra | Privileges | Comment |
>>>
>> +-----------+--------------+-------------------+------+-----+-
>> --------+-
>>> ------+------------+---------+
>>> | query     | varchar(150) | latin1_swedish_ci |      |  
>> PRI |         |
>>> | select     |         | | pageviews | int(11)      | NULL
>>> | YES  |     | NULL    |
>>> | select     |         | | date      | varchar(10)  | 
>> latin1_swedish_ci
>>> | YES  |      | NULL    | | select     |         |
>>>
>> +-----------+--------------+-------------------+------+-----+-
>> --------+-
>>> ------+------------+---------+ 
>> -- 
>> Stewart Smith, Senior Software Engineer
>> MySQL AB, www.mysql.com
>> Office: +14082136540 Ext: 6616
>> VoIP: 6616@stripped
>> Mobile: +61 4 3 8844 332
>>
>> Jumpstart your cluster:
>> http://www.mysql.com/consulting/packaged/cluster.html
>>
> 

-- 
high performance mysql consulting
www.provenscaling.com