Use CHECK with LIKE in MySQL?

Question

I'm new to mysql, my question is that is this correct?

CREATE TABLE ... (
    /* some definition ommitted. */
    About varchar(300) NOT NULL DEFAULT '...',
    CHECK(About LIKE 'ABOUT: %, DATE: %')
);

By searching, all results show LIKE within SELECT, so is it syntactically correct to be used in CHECK?

@bigbounty: But I didn't found and search result, even if this would work, it might be a bad practice. And for some reason I cannot test this right now. — NeoZoom.lua, Mar 30 '21 at 12:19

score 0 · Answer 1 · answered Mar 30 '21 at 20:10

Yes, syntactically that is OK.

But two things:

Be aware that below version 8.0.16, MySQL does accept check constraints in a table DDL but it doesn't enforce the check constraints. so it's like they weren't there at all.

You should check if that's applicable to your version.
And more importantly, you seem to make a big mistake there, violating the first normal form.

It seems like there are two attributes here, some sort of textual description and some point in time. That should really be two different columns.

Don't put their values together in one big text column!

Otherwise I'll guarantee you a lot of headaches in the future:
- Every time you want to query one of the two attributes you first need to parse the entire text.
- You cannot create any useful indexes that help boost performance.
- Updating one attribute means changing the whole text but in the right spot instead of just the attribute itself.
- And so on and so on...

1 Answers1