• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1315
  • Last Modified:

Single quotes in a query

Hi,

I have a table full of phone numbers in the format 1112223333.  The table has about 4 million records in it and I update the numbers in it once a month.  (In this case, updating means deleting all records and inserting new records from a file via  LOAD DATA INFILE)
I did a test query using the command line mysql utility, before I dumped the records with a record I knew was in there. The query was:

mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234

Then I dumped my records and loaded my updated set.
I did the same query above (I know that the number 2124451234 is in the table.
This time though the query brought back nothing. I triple checked to make sure that number was actually in the table, and it was.

So, I tried doing the query without the single quotes:
mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234 however it took much longer to bring it back.

A few other things of note, I have an normal index on the phone field.
The phone field is a char field.

I'm scratching my head here wondering why I can't get a result when using the single quotes. I have always been able to before.
So, I guess my first question is what is the difference between running the query with the quotes and without them?

The only thing I can think that is causing this problem is the data file because it's the only thing that has changed.  The data file I'm loading has one phone record per  row. If I open the datafile in a Hex editor, each row is terminated by an 0A character.  I don't have an old datafile to compare this with so I'm not sure if all my datafiles in the past have had this character at the end of each row.  My LOAD DATA INFILE command using the default record separator (\n).  Viewing the contents of my table after loading this data file in, shows one record per row. I'm wondering though if this  0A character could be causing this problem with my single quoted query not returning correctly.  

Any suggestions are welcome.

Thanks,
mike




0
michaelshavel
Asked:
michaelshavel
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this could be dues to either a different data type on the other table, or a missing index on that table.
please cross check what differences you have on the table specs an indexes...


0
 
Steve BinkCommented:
The two queries you gave for your example are identical.  See here for more detailed information on data types in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

The single-quote character denotes a literal string in MySQL.  For example:

SELECT * FROM MyTable WHERE Field1=2

That query will search for all records where Field1 equals the NUMBER 2.  If you add quotes:

SELECT * FROM MyTable WHERE Field1='2'

Now the query looks for records where Field1 equals the literal string '2'.  MySQL has loose typing, which means it will try to coerce data into the type it thinks it needs.  Going from a number to a string is not very hard, because MySQL can just put single-quotes around the number and there you go.  Going from a string to a number is a little more difficult because not all strings have a numeric equivalent.  Spaces, punctuation, and other non-numeric data will affect how MySQL translates it.  

Another issue is that MySQL uses different sorting algorithms for numbers and strings.  In a number sort, you get this sort result:

1
2
10
20
100
200

In a string sort with the same data, you would receive this:

1
10
100
2
20
200

If your field is a CHAR field, you will almost definitely need the single-quotes to perform the query.  In some case it doesn't matter, but when in doubt, use them.
0
 
michaelshavelAuthor Commented:
Hi Routinet.
Your right the two queries are identical. It was a typo in this posting.

The second should read:
mysql>SELECT phone from phonelist WHERE phone=2124451234;
This query brought back 2124451234 but it took a few seconds for mysql to bring back a result.

Thanks,
mike





0
 
michaelshavelAuthor Commented:
FYI -- this is my index. Anything look out of the ordinary here?


mysql> show index from phonelist;
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phonelist |          1 | phonenumbers |            1 | phone       | A         |     4167773 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
0
 
michaelshavelAuthor Commented:
Thanks. I found the problem.
My Input file was modified by the sender. Each record had 0D0A as a record separator. The first file I was using just  had  0A as the record separator.


0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now