7

On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change - that's why I'm not using it as the primary key.

In my program's code, I have a load function. First thing it does is check the richter_code field for the search parameter. If it doesn't return anything, it then goes on to search the on the ID field for the same parameter.

The problem is that it seems to truncate the value as soon as it encounters an alpha character. So I'm getting complete garbage from my query. See screenshot for example. Can I prevent mysql from changing the query?

screenshot

  • I don't know about autocasting and truncation, but try with SELECT * FROM cloths WHERE CAST(id AS VARCHAR) = "3R100C" – watery Jan 20 '15 at 23:51
  • I can't cast it to varchar. Syntax error :( – Shane Jan 21 '15 at 14:37
  • Does the autoincremented Id column have any business meaning? Why would the progran even try to use it for a search? – Mathieu Guindon Jan 21 '15 at 15:00
  • 1
    You can, I just suggested the wrong type (I didn't check the docs). Replace VARCHAR with CHAR and you should be good to go. – watery Jan 21 '15 at 16:16
7

Gotta love MySql ;)

I searched Stack Overflow for a MySql equivalent to some isnumeric function, and found this.

This rather convoluted WHERE clause would return the expected 0 rows:

where Id = case when concat('','3R100C'*1) = '3R100C' then '3R100C' else null end

The idea is to only match the parameter against Id when it is a valid numeric value. Hope it helps!


I'd create an actual isinteger function to abstract away this sheer madness:

CREATE FUNCTION `isinteger`(v varchar(255)) RETURNS bit(1)
BEGIN

    RETURN CASE WHEN CONCAT('', v * 1) = v THEN 1 ELSE 0 END;

END

That said I don't think it's right to use an auto-incremented Id field as a search-fallback column - this ID is your table's primary key, its meaning belongs strictly on the database side, no program should ever care about it, even less a program's user.

  • Note that this function will return 0 in the event of an integer overflow.. which isn't technically wrong ;) – Mathieu Guindon Jan 21 '15 at 16:15
  • 1
    I think you could use else null instead of else -1 – ypercubeᵀᴹ Jan 21 '15 at 16:18
  • 1
    I'd be worried by the performance of calling a custom function (performance penalty that, if any, I don't know). A benchmark of both solutions would be interesting. – watery Jan 21 '15 at 16:20
  • @ypercube good point, edited ;) – Mathieu Guindon Jan 21 '15 at 16:24
  • Besides the *1 trick, this might also work: WHEN CAST(CAST('3R100C' AS int) AS char) = '3R100C' – ypercubeᵀᴹ Jan 21 '15 at 16:41
5

The problem here is that MySQL is doing an implicit conversion from the string to an int when doing the comparison....

SELECT * FROM cloths WHERE id = "3R100C"

making the conversion explicit, this is what MySQL is doing:

SELECT * FROM cloths WHERE id = CAST("3R100C" as int)

Instead, what you want to do is cast the id to a string, and compare that way. The solution is simply to:

SELECT * FROM cloths WHERE CAST (id as varchar(10)) = "3R100C"

That will remove any need for the integer handling/parsing on the string side.

  • This would be correct but rather horrible for performance. – ypercubeᵀᴹ Jan 21 '15 at 16:36
  • @ypercube - you're right, it will require an index-scan for all records. The alternative is to do the string-is-integer check, and cast it if it is, and then do the index lookup with that. Depending on the plan, it may well do the cast and check even for values that have non-digit content. Actual testing in the actual environment will be important. – rolfl Jan 21 '15 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.