Varchar for columns that store numerics/ids?

Question

There is a table that has a column defined as follows:
text_id varchar(255) NOT NULL I know that using varchar is optimal for storing text because it allows for variable length columns but in this case I am not sure if there is any id stored that is not in the form of a number so it is not clear to me if there is any benefit to store it in this data type instead of e.g. unsigned int.
Is there any benefit using this definition and how can I check if there is any value in the column that is not a numeric but some form of alphanumeric id?
I tried where text_id REGEXP '^[A-Za-z0-9]+$' limit 10 but this matches also pure numbers.

Note: the table has too many rows to just do a SELECT * and review

Answer 1

Your regular expression is looking for strings that contain only alphanumeric characters, that is why it picks out pure numbers – you've told it to look for them too. It breaks down as:

    ^            = start of string
    [A-Za-z0-9]+ = one or more letter or number character
    $            = end of string

To match those that are pure numbers you want REGEXP '^[0-9]+$' and you can invert the logic with WHERE NOT (text_id REGEXP '^[0-9]+$') to find problem rows. Possibly not the most efficient way but this sort of lookup is going to table-scan (or at beat index-scan) so I expect nothing will be significantly better.

To answer the general question:

Is there any benefit using this definition [varchar for an ID]

The only benefit is if an ID could be not purely numeric. To know if that might be the case you need to know the system and its data sources so we can't answer that. Perhaps the DB designer explicitly picked a text representation because they can't control what future systems integrating with this one might want to use so couldn't dictate that this identifier will always be numeric going forward even if it is always numeric currently.

Generally speaking if the IDs are always going to be numeric then using an integer type of appropriate size will be more efficient, in terms of both storage and CPU use, than a text representation. There is one thing to be careful of if moving from text representation of identifiers to a numeric representation which is leading zeros: sometimes systems enter something like 000123 as an ID string and expect to get 000123 back when they read, which they won't with an integer type (the leading zeros will be lost).

Even if you are sure the IDs are always numeric and won't have leading zeros, make any change with caution: if an application is expecting a string type it may fail even if the data represented is no different. Check your change in a test environment before doing it in production. Also you may need to drop foreign-key constraints referring the ID before you can change the type, and need to change the type of the referring columns before putting the FK back, so it could be a much larger job than just updating this one column.

One further point: as the column is explicitly named text_id, changing its type to a numeric will make that contradictory and confusing.

Answer 2

WITH cte (text_id) AS (
  SELECT '1' UNION ALL   -- number
  SELECT '123' UNION ALL -- number
  SELECT '1a1' UNION ALL -- not number
  SELECT '  2' UNION ALL -- not number
  SELECT '3  ' UNION ALL -- not number
  SELECT 'asd' UNION ALL -- not number
  SELECT 4               -- number
)
SELECT *
FROM cte
WHERE text_id NOT REGEXP '[^\\d]'

text_id
1
123
4

fiddle