[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysqldump with there where clause

Posted on 2006-04-04
5
Medium Priority
?
819 Views
Last Modified: 2010-02-27
ive seen the examples of using "where" clause when the fieldname = a numeric value...ie: salesID = 109 ---that works great. returns all the data specified.

where i'm having issues is when i try to use an alphanumeric value...

ie:

mysqldump -h localhost -u jsmith --password=MyPassw0rd --no-create-info --where='sales_person'='Gary' database tablename;

i get nothing....i have a feeling it may be the quotes but ive tried everything and either get a parse error or no results.

any ideas? thx


0
Comment
Question by:nriddock
  • 2
4 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16372767
Did you try this:

mysqldump -h localhost -u jsmith --password=MyPassw0rd --no-create-info --where=sales_person='Gary' database tablename;
0
 

Author Comment

by:nriddock
ID: 16372826
yep...it echoes the query but returns no results...is it possible that mysqldump only works  w/ numeric values?
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 2000 total points
ID: 16372916
No - it works for me.  Check the output below - the first run was executed without any --where parameter and includes all 4 rows.  The second includes a --where=name='test2' parameter, and includes only the corresponding row.

C:\Documents and Settings\Todd>mysqldump -uroot -pxxxxxx test tablea
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.18-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tablea`
--

DROP TABLE IF EXISTS `tablea`;
CREATE TABLE `tablea` (
  `id` int(11) default NULL,
  `name` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tablea`
--


/*!40000 ALTER TABLE `tablea` DISABLE KEYS */;
LOCK TABLES `tablea` WRITE;
INSERT INTO `tablea` VALUES (1,'test1'),(2,'test2'),(3,'test3'),(4,'test4');
UNLOCK TABLES;
/*!40000 ALTER TABLE `tablea` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


C:\Documents and Settings\Todd>mysqldump -uroot -pxxxxxx --where=name='test2' te
t tablea
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.18-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tablea`
--

DROP TABLE IF EXISTS `tablea`;
CREATE TABLE `tablea` (
  `id` int(11) default NULL,
  `name` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tablea`
--
-- WHERE:  name='test2'


/*!40000 ALTER TABLE `tablea` DISABLE KEYS */;
LOCK TABLES `tablea` WRITE;
INSERT INTO `tablea` VALUES (2,'test2');
UNLOCK TABLES;
/*!40000 ALTER TABLE `tablea` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
0
 
LVL 6

Expert Comment

by:campbelc
ID: 16373995
Notice in your example you have tick marks around 'sales_person' take out the tick marks and try again.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 7 hours left to enroll

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question