Why does it fail to spell out collation correctly?
> >> 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 | |
+-----------+--------------+-------------------+------+-----+---------+-
------+------------+---------+
> -----Original Message-----
> From: Stewart Smith [mailto:stewart@stripped]
> Sent: Tuesday, October 23, 2007 6:30 AM
> To: Kathryn Steinbrink
> Cc: internals@stripped
> Subject: Re: SHOW CREATE TABLE and auto_increment
>
> Hi Kathryn!
>
> On Mon, 2007-10-22 at 23:09 -0500, Kathryn Steinbrink wrote:
> > It is documented in the MySQL 5.1 Reference that the SHOW
> CREATE TABLE
> > statement can be used to show the statement syntax that was
> used to create
> > the specified table. For the auto_increment table option,
> the value that
> > is returned is the 'next value' that will be generated for the
> > auto_increment column. Offhand, this seems wrong. I would
> have expected
> > the original auto_increment= value to be displayed that was indeed
> > specified on the original CREATE TABLE (or ALTER TABLE)
> statement. Can you
> > please comment?
> >
> > If a storage engine has access to the original value, is
> it okay to return
> > the original value instead of the 'next value'? (If not, why not?)
>
> The (real) purpose behind SHOW CREATE TABLE is to enable things like
> mysqldump to exist.
>
> However... users like it too :)
>
> The aim is to produce an SQL statement that will create the
> table as it
> currently is (all ALTERS etc taken into account). Since the
> auto_increment column is affected by normal queries, it should also be
> taken into account as it's part of the current state.
>
> i.e. SHOW CREATE TABLE is showing the CREATE statement needed
> to create
> the table as it exists *now*.
>
> Any other behaviour (e.g. original auto_inc value) would be
> inconsistent
> with the rest of the output (else you'd expect CREATE + a bunch of
> ALTER TABLE).
>
> You could possibly display this information in the comment
> field (which
> ends up in INFORMATION_SCHEMA at least).
>
> If you have other metadata that makes sense to export,
> INFORMATION_SCHEMA plugins are rather easy too, so you could always
> export it there.
>
> hope this helps,
> stewart
> --
> 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
>
