1. looks to me like the it does NOT differ from the default??
2. What good is COLLATE on the CREATE TABLE if it is totally ignored by
the column?
> -----Original Message-----
> From: Jeremy Cole [mailto:jeremy.cole@stripped] On
> Behalf Of Jeremy Cole
> Sent: Saturday, November 03, 2007 10:08 AM
> To: Rick James
> Cc: Stewart Smith; Kathryn Steinbrink; internals@stripped
> Subject: Re: SHOW CREATE TABLE and auto_increment
>
> 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
>
