编写正则表达式匹配MySQL文本字段中的两类数据

Got it, let's tackle these two regex patterns for your MySQL text fields. I'll break down each one with clear explanations so you can adapt them if needed.

Your data1 is a comma-separated list of single numbers, with optional spaces around commas, and no leading/trailing commas. Here's the regex you need:

^\\d+(?:\\s*,\\s*\\d+)*$

Breakdown of the pattern: #

• ^ and $: Anchor the match to the start and end of the field, ensuring we don't get partial matches (like a valid list embedded in garbage text).
• \\d+: Matches one or more digits (covers any positive integer, as per your example).
• (?:\\s*,\\s*\\d+)*: A non-capturing group that handles the separators:
  • \\s*: Matches zero or more spaces (covers any whitespace around commas, including none).
  • ,: The literal comma separator.
  • \\d+: Another number to continue the list.
  • The * at the end means this group can repeat zero or more times (so a single number is also a valid match, which makes sense if n=1).

To use this in a MySQL query, it might look like:

SELECT * FROM your_table WHERE data_column REGEXP '^\\d+(?:\\s*,\\s*\\d+)*$';

For data2, you have comma-separated text entries (which can include spaces, like "Lorem ipsum"), with optional spaces around commas, and no leading/trailing commas. Here's the regex:

^[^,]+(?:\\s*,\\s*[^,]+)*$

Breakdown of the pattern: #

• ^ and $: Again, anchors to ensure full-field matching.
• [^,]+: Matches one or more characters that are not commas. This allows text entries with spaces, letters, and other non-comma characters (perfect for your example phrases).
• (?:\\s*,\\s*[^,]+)*: The same separator logic as the numeric pattern, but instead of matching digits, we match any non-comma text. This ensures each entry is separated by a comma (with optional spaces) and there's no trailing comma.

Example query usage:

SELECT * FROM your_table WHERE data_column REGEXP '^[^,]+(?:\\s*,\\s*[^,]+)*$';

Quick Notes: #

• If you need to enforce case sensitivity (e.g., distinguish between "Lorem" and "lorem"), add the BINARY keyword before REGEXP: WHERE BINARY data_column REGEXP ...
• These patterns assume your text entries don't contain commas themselves (since commas are used as separators). If that's a possibility, you'd need a more complex regex (but your original description says data2 uses commas as separators, so this should work).

内容的提问来源于stack exchange，提问作者Zoli