3

I'm trying to prevent mysql from casting a string to an int in a where clause.

The following query returns the correct row for the order

SELECT delivery_name FROM orders WHERE orders_id = '985225'

The following query also returns the same row as it is casting my string to an int, but I'd like to prevent it from returning the row at all (ie, not cast the string given to an int and effectively say, this cannot accurately be cast to an int so don't).

SELECT delivery_name FROM orders WHERE orders_id = '985225a'

I hope that makes sense, any help would be greatly appreciated!

Improve this question
2
  • 4
    You should be checking for non-int stuff in your client code, before you ever let it near the query string. Commented Dec 20, 2013 at 16:48
  • I don't think you can convince MySQL to do what you want (and as most other DBMS behave) Commented Dec 20, 2013 at 16:53

3 Answers 3

Reset to default
1

You should be able to avoid this scenario altogether, but if you wanted a workaround you could concat a character to the front of the string, something like:

SELECT delivery_name 
FROM orders 
WHERE CONCAT('a',orders_id) = CONCAT('a','985225a')
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

downvoted, even if that can work it ill render useless any index on order_id forcing a full table scan over orders table.
@jean Upvoted, no one is talking indexes here but just about a trick to get what the OP wants. Come on.
@jean Yes, this scenario should be avoided, but there is a valid workaround, which I've provided.
I totally agree that this situation should be avoided and in the client code that would be the usual place to do it, but in this one situation doing it the mysql side would be ideal. This answer would render any index useless but that wasn't actually part of the question (and in the situation not a problem either) as the table would only ever have a handful of rows at a time. Answer accepted, thanks @GoatCO
Since OP accepted the answer I tried to remove the down vote but cannot do it unless answer is edited.
0

I suggest you to pas that variable as a integer from your application.

SELECT delivery_name FROM orders WHERE orders_id = '985225'

If orders_id is a integer it means for every row in orders engine ill implict cast it to string to evaluate orders_id = '985225' its not just slow but ill render useless any index in orders_id.

if all you can do is edit the SQL i suggest you to cast the variable.

orders_id = CAST('985225' AS INT)

And one final hint, at application layer its more easy do validate input and prevent a non valid integer input from user.

Improve this answer

1 Comment

thanks for the answer, but I believe that mysql actually converts the string that I give '985225' to the int 985225 as opposed to converting every int in the table to a string. So, yes, there would be a performance increase by passing an int instead of a string, but it would be negligible in most situations and the index would still be just a useful as if an int was passed.
0

Step 1 - remove the single quotes from the sql string. In other words, change this:

WHERE orders_id = '985225'

to this:

WHERE orders_id = 985225

Step 2 - as per @Marc B's comment, ensure you actually have an integer.

Step 3 - Use a query parameter. MySql accepts them as far as I know.

Improve this answer

1 Comment

Removing the quotes from the orders_id makes negligible speed difference even in a big table with an index (and also would actually prevent the '985225a' value from being able to be sent). In the one situation, I don't want to make sure I actually have an int in the client code... that was the point of the question. I'm not sure what a query parameter is, an explanation would have been helpful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.