CREATE TYPE on MySQL

Question

I couln't create a new data type on MySQL. Query is given below

CREATE TYPE PERSON AS OBJECT
(NAME       VARCHAR (30),
 SSN        VARCHAR (9));

How can I do that on MySQL

Answer 1

The other two answers are 100% correct, both that you cannot define new scalar-tuple values, and that JSON as a native type gets you almost there.

I have three suggestions. First, a proposal for how to enforce a specific JSON schema on value, supported by getters/setters. A second possible solution would by CTE's, though that involves some additional work on the writing end, and is not a replacement for true custom types. A third approach, somewhat of a hybrid of the first two suggestions, would involve getters/setters that encapsulate a subquery lookup.

Please, before you read further, the following represents napkin-draft quality sketches. Expect nothing better. I haven't worked with MySQL since late 2020.

First up, a constructor/getter/setter approach, though implementing this would likely take a bit of work, and even well-designed indexing can't get it to compete with native columns on lookup performance, and it'll always take up more space in chunk/page/disk but it's a 90% solution -- much like how one implements struct methods in C, if you do it, it's solely for namespace organization, and it will be clumsy.

delimiter $$;
create function contact_encode(fname text, lname text, phone text, email text)
returns JSON
deterministic
begin
    /* validation stuff here? like maybe you care that they're not null
       and/or sane values for names and phone numbers and email addresses?
     */
    return json_object(
        'firstName', fname,
        'lastName', lname,
        'contactMethod', json_array(
            json_object(
                'type','phone',
                'value',phone
            ),
            json_object(
                'type','email',
                'value',email
            )
        )
    );
end$$
delimiter ;$$
/*  Yep, this approach probably actually sucks

    I was going to suggest something like contact_decode(JSON)->values...,
    but I forgot that you can't return multiple values from a function.
    That's the irony; it'd be easier to use JSON for custom types in
    languages that already support return tuples and custom types as relations. 

    Grrrrr
 */

Err, yep. That's probably the closest you can get to custom types using MySQL. Hypothetically a set of getter/setter functions like the following would actually conceivably improve on the existing MySQL 8 API for JSON values/columns. This is, again purely hypothetically, an example of a setter with encoded behaviors that provides some level of encapsulated logic:

create function contact_get_name(contact JSON)
.....return concat(contact->>'firstName', ' ',contact->>'lastName')
.....
create function contact_set_name(contact JSON, value text)
....return json_replace(
        contact, 
        '$.firstName', substring_index(value, ' ', 1),
        '$.lastName'. substring_index(value, ' ', -1)
    )
.....

Issues abound. If it helps, typing wasn't ever a serious constraint on me as a MySQL dev. Now that I work with an outfit that uses PostgreSQL as the default, custom types are amazing (return rowtype, I'm looking at you), but because we hadn't migrated from MySQL 5.x to 8.x, I didn't get to play with CTE's before PostgreSQL. That said, the following might not translate directly to MySQL.

Suppose a table called 'blog_user' has the columns (id, name, phone, email, username, password, blah-blah-blah.....), a 'custom type' use of the table in a query of a table called post (id, author_id, title, ....) might look like:

with author as (
    select
        id,
        substring_index(name, ' ', 1) as first_name,
        substring_index(name, ' ', -1) as last_name,
        email,
        concat('###-###-', right(phone, 4)) as phone
    from blog_user
) select
    concat(first_name, ' ', left(last_name, 1), '.') as name,
    title,
    yada-yada.....
from author, post
where author.id = post.author_id
and case 
    when @requested_blog_post is not null
        then post.id = @requested_blog_post
    when @title_search_term is not null
        then post.title like concat('%',@title_search_term,'%')
    else false
end

It achieves the desired impact, and as a partial join, it's actually going to be pretty much transparent, performance-wise...assuming good indexes on any CTE source columns used in the core query's filter.

Performance-wise, the latter approach, adding CTE's to create subqueries to partially join in on specific attributes, is going to be far-and-away superior to the JSON approach, while the JSON approach is arguably more ergonomic to some, especially if you're accustomed to working with JSON in MySQL anyway.

A third solution would be to define custom getter/setter functions that receive an id, and which return the value for the column on that id, like so:

create function post_get_author_name(requested_post int)
....
return (
    with author as (
        ....
    ) select 
        name 
    from author, post
    where author.id = post.author_id
    and post.id = requested_post
    limit 1 /*completely unnecessary in this case, but it's a thing I do anyway*/
)
....

The difficulty with that approach is that, well, it suggests you're going to call a bunch of those getters in the same query. That's objectively worse performance-wise than the local CTE solution, and it's possibly worse performance than the same getter/setter approach with row-local JSON.

The take-away is the following: sorry you can't have custom types. There are some approximations, and I wouldn't have even understood your pain a few months ago, but my condolences. Have some possibly workable approximations.

Answer 2

You can use JSON data type now. MySQL supports the native JSON data type since version 5.7. 8.

Answer 3

You cannot. MySQL doesn't have user-defined types. (At least, not in current versions.)