How to use a generated column in MySQL using MySQLWorkbench

#1

I’m using MySQLWorkbench. I would like to create a Generated column that would calculate the date difference and store the result. Attached is an image of the scheme.

Screen Shot 2020-02-28 at 5.31.27 AM
Screen Shot 2020-02-28 at 5.31.27 AM1344×154 27.5 KB

#2

Hey there @BruceSchueller…

Well guess this a MySQL question, as MySQL Workbench is just a window into your database…

I would either:

  1. Create a mysql stored procedure which calculates the difference and then creates or updates the table row. You can then call the procedure from Wappler via a Custom Database Query… Or:

  2. Create a mysql trigger which fills in the difference field when one of the dependent fields changes.

Best wishes,
Antony.

#3

Hi Antony.

Thanks for the response. I am aware of those options, however, that’s why there exists a “Generator” or “G” checkbox in MySQLWorkbench. When “G” is checked you can add the command in the “default” field and by doing so eliminates the use of a Stored Procedure, Trigger, or the creation of a View. I tried different syntax and datatypes with no success. UGH!

#4

I don’t use MySQL Workbench, but the syntax should be something like this:
``title_sort varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (left(lower(trim(leading 'a ' from trim(leading 'an ' from trim(leading 'the ' from trim(leading ' ' from lower(title)))))),20)) STORED NULL

This is a generated column I use for sorting. One advantage of using generated columns is that they don’t have to take up any extra space - they can be STORED or VIRTUAL (where the values are generated on the fly). Also, it makes queries simpler as you don’t have to include the extra SQL to achieve what you’ve already done in the generated column. From a Wappler point of view, it means you can use the Query Builder in cases where you would otherwise have to use a Custom Query.

If you don’t have this option, make sure the version of MySQL you’re using supports generated columns - I think they’re fairly new.

#5

Thanks for the feedback folks… looks like generated columns are something for me to investigate! :slight_smile:

#6

I certainly find them useful from time to time. Another advantage over using triggers is (not surprisingly) that they don’t have to triggered - they will always have the expected value.

#7

So true. Still frustrated in that I can’t get it to work with DATEDIFF in MySQLWorkbench. UGH!!