SEO

I'm trying to dump all the columns of a table but one in a
restructuring of my schema. I found this post:
http://www.jsw4.net/info/listserv_ar.../msg00131.html
which seems to indicate that this isn't possible, but I was wondering
if anyone had any suggestions. My current solution is to do a simple
search and replace, but that won't work for everything. I could
always do regex search/replace, as well, but I'm hoping for something
native to mysqldump, since the files are going to be rather big.

Thanks,
Waynn

On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue <waynnlue@gmail.com> wrote:
> I'm trying to dump all the columns of a table but one in a
> restructuring of my schema. I found this post:
> http://www.jsw4.net/info/listserv_ar.../msg00131.html
> which seems to indicate that this isn't possible, but I was wondering
> if anyone had any suggestions. My current solution is to do a simple
> search and replace, but that won't work for everything. I could
> always do regex search/replace, as well, but I'm hoping for something
> native to mysqldump, since the files are going to be rather big.
>
> Thanks,
> Waynn

I would probably use INTO OUTFILE. Any particular reason it does not work?
CREATE TABLE `t` (
`c1` varchar(10) NOT NULL,
`c2` varchar(10) NOT NULL,
`c3` varchar(10) NOT NULL
);

INSERT INTO `t` (`c1`, `c2`, `c3`) VALUES
('1', '2', '3'),
('4', '5', '6'),
('7', '8', '9'),
('10', '11', '12');

mysql> select c1,c2 from t INTO OUTFILE 'outfile';
Query OK, 4 rows affected (0.00 sec)


outfile contents:
1 2
4 5
7 8
10 11






--
Rob Wultsch

> On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue <waynnlue@gmail.com> wrote:
>> Subject: Re: mysqldump on specific columns only?
>> I'm trying to dump all the columns of a table but one in a
>> restructuring of my schema.

By "dump", I assume you mean "output", not "delete".

I suspect I'm too inexperienced in MySQL or I don't understand the
question, because I don't see why you haven't mentioned
ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original
database but rather a copy database: mysqldump, load it into another
database, ALTER TABLE DROP COLUMN. It's true that you wrote

>> since the files are going to be rather big.

The second notion has the disadvantage that the data will be copied
three times (mysqldump, load, ALTER TABLE). On the other hand, you
have the full reliability of mysqldump, with its care in copying
CREATE TABLEs and data correctly.

Am I perhaps misunderstanding?


On Sun, 9 Mar 2008, Rob Wultsch <wultsch@gmail.com> wrote:
> I would probably use [SELECT] INTO OUTFILE. Any particular reason it
> does not work?

It's not well-suited for "restructuring of my schema". mysqldump
emits SQL that can be used immediately to create the table with
exactly the same data types and column names and keys and other
properties (like NOT NULL), and to insert data into columns with
quoting and escaping as needed, and nice little flourishes like DROP
TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES.

> mysql> select c1,c2 from t INTO OUTFILE 'outfile';
> Query OK, 4 rows affected (0.00 sec)
>
>
> outfile contents:
> 1 2
> 4 5
> 7 8
> 10 11

That provides only the data to insert. Further, it would require
considerable massaging to get it into the form of INSERT statements,
though for all I know MySQL provides other mechanisms to load that
sort of data. But those are tab-separated columns: what if one of the
columns were a text field with a literal tab?

--
Tim McDaniel, tmcd@panix.com

Thanks for the responses, Inline:

On Sun, Mar 9, 2008 at 7:49 PM, Tim McDaniel <tmcd@panix.com> wrote:
> > On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue <waynnlue@gmail.com> wrote:
> >> Subject: Re: mysqldump on specific columns only?
>
> >> I'm trying to dump all the columns of a table but one in a
> >> restructuring of my schema.
>
> By "dump", I assume you mean "output", not "delete".

Indeed, I meant "output."

> I suspect I'm too inexperienced in MySQL or I don't understand the
> question, because I don't see why you haven't mentioned
> ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original
> database but rather a copy database: mysqldump, load it into another
> database, ALTER TABLE DROP COLUMN. It's true that you wrote
>
>
> >> since the files are going to be rather big.
>
> The second notion has the disadvantage that the data will be copied
> three times (mysqldump, load, ALTER TABLE). On the other hand, you
> have the full reliability of mysqldump, with its care in copying
> CREATE TABLEs and data correctly.
>
> Am I perhaps misunderstanding?

ALTER TABLE unfortunately takes longer than dumping and recreating the
tables. I started a previous thread on this, and the consensus was
recreating everything was faster, and I'm taking advantage of this
downtime to restructure our schema, and add a few other columns that
will be needed. The initial impetus was expanding an INT to a BIGINT,
but in the presence of a foreign key. So that would require dropping
the FK, expanding the INT to BIGINT in two tables, then recreating the
FK. I would also want to add three more columns while I'm at it. And
in this case, the cost of all those operations is significantly higher
than just dumping all the tables, truncating/dropping the tables,
recreating the tables, then importing.

> On Sun, 9 Mar 2008, Rob Wultsch <wultsch@gmail.com> wrote:
> > I would probably use [SELECT] INTO OUTFILE. Any particular reason it
> > does not work?
>
> It's not well-suited for "restructuring of my schema". mysqldump
> emits SQL that can be used immediately to create the table with
> exactly the same data types and column names and keys and other
> properties (like NOT NULL), and to insert data into columns with
> quoting and escaping as needed, and nice little flourishes like DROP
> TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES.

I'm actually not going to be using the exact same schema anyway, so
SELECT INTO does work, but ... let me continue this thought below.

> > mysql> select c1,c2 from t INTO OUTFILE 'outfile';
> > Query OK, 4 rows affected (0.00 sec)
> >
> >
> > outfile contents:
> > 1 2
> > 4 5
> > 7 8
> > 10 11
>
> That provides only the data to insert. Further, it would require
> considerable massaging to get it into the form of INSERT statements,
> though for all I know MySQL provides other mechanisms to load that
> sort of data. But those are tab-separated columns: what if one of the
> columns were a text field with a literal tab?

MySQL does provide LOAD DATA INFILE
(http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was
hoping to do it through mysqldump instead of SELECT INTO because I
assumed the performance was faster. If that's not the case, though,
I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I
guess most any character is fine as a delimiter, since my fields
should all just be IDs...

Thanks,
Waynn

> MySQL does provide LOAD DATA INFILE
> (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was
> hoping to do it through mysqldump instead of SELECT INTO because I
> assumed the performance was faster. If that's not the case, though,
> I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I
> guess most any character is fine as a delimiter, since my fields
> should all just be IDs...
>
> Thanks,
> Waynn
>

OK. Why not just create the new table and SELECT... INSERT . I would
think that would be the fastest solution of all.

--
Rob Wultsch