Why does MySQL always return no data for this query?

Question

I have a database in which a few fields are set as index. I don't know if this has to do with the problem or not, but when creating a query I get no results. Here is how I have things set in the database. The database has 90,000 records and this table is set like:

| fieldA  | fieldB | fieldC  | fieldD |

fieldA = index - CHAR
fieldB = index - CHAR
fieldC = index - CHAR
fieldD = index - VARCHAR

If I do:

SELECT * 
  FROM tableA 
 WHERE 'fieldD'='A LONG STRING WITH SYMBOLS AND CHARCTERS';

I get 0 results and I know that the value for fieldD exist and is there.

What am I doing wrong?

Answer 1

Throw away apostrophes around field name, You may use them but apostrophes around field names are opposite = "´"

SELECT * FROM tableA WHERE fieldD='A LONG STRING WITH SYMBOLS AND CHARCTERS'

Answer 2

Not sure if this was a typo in your query above, but fieldD should not be in single quotes.

Answer 3

It looks to me like your problem is you're enclosing your column names with single quotes. This is causing MySQL to treat it as a string instead of a column name, so you are literally comparing the string 'fieldA' with long string. Remove the quotes from around the column name and you should be good to go.

SELECT * FROM tableA WHERE fieldD='A LONG STRING WITH SYMBOLS AND CHARCTERS';

Answer 4

There could be a problem with character encoding here. I would try this query and see what happens: SELECT * FROM tableA WHERE 'fieldD' like '%A LONG STRING WITH SYMBOLS AND CHARCTERS%';

Also, I noticed a typo on the word 'CHARACTERS'.

Answer 5

BACKTICKS instead of single quotes

SELECT * FROM tableA WHERE `fieldD`='A LONG STRING WITH SYMBOLS AND CHARCTERS';

The backticks key is to the left of the 1 key

Answer 6

I don't know why but the following query did the trick. If any of you can explain why will be appreciated:

SELECT * FROM tableA WHERE completo LIKE ( '1008017 P MT/75 FRONT 52H%' ) LIMIT 0 , 30

I had to place a %' at the end of the string to work

Answer 7

the column does not have the data in it you think it does. try this:

first run this query:

select hex('100/80R16 M A39 50S')

then run this query:

select fieldD, hex(fieldD) from tableA

then compare the results from the first query to the appropriate row from the second query. i think you will find the hex() representations do not match.

if you can't tell why they don't match from the hex() output, post it here and i'll help you figure out why it's not working.