Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] 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_archives/mysql/05-wk49/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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
Thanks for the responses, Inline: On Sun, Mar 9, 2008 at 7:49 PM, Tim McDaniel [EMAIL PROTECTED] wrote: On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 1011 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
