2

I'm thinking about using https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field, but it doesn't say anything about any limits in terms of how many values it can support.

Does anyone have any insight into this? Thanks!

Bob Risky
  • 805
  • 1
  • 9
  • 22
  • 2
    My general intuition when stuff like that is omitted from MySQL's documentation, from years of referencing it, is it usually means "large enough you shouldn't have to ask" and "if you have to ask, you shouldn't probably be using it for whatever it is you're asking in reference to". _Kinda like how the limit of the number of WHEN-THENs in a CASE expression is not spelled out._ – Uueerdo Feb 05 '19 at 23:29
  • I'd recommend either normalising the tables properly or storing json https://duckduckgo.com/?q=mysql+json&atb=v110-5_b&ia=web – Keith John Hutchison Feb 05 '19 at 23:31

1 Answers1

1

I'm not aware that there's any limit on the number of arguments to the FIELD() function per the grammar. I assume it's limited by the max length of an SQL query.

The limit on the length of any SQL query is determined by max_allowed_packet in bytes. The default for this variable in MySQL 5.7 is 4MB. It can be increased up to 1GB. https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

I agree with @Uueerdo — If you're generating a list of expressions longer than 4MB, you should really question if your approach to your task is the best way of solving it.

I'd guess that the FIELD() function will compare the arguments in a linear fashion, because the implementation probably never guessed that anyone would feed it very long lists. So searching through tens of thousands of arguments for every row will probably be a bad performance bottleneck.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828