MySQL Forums
Forum List  »  InnoDB

Is virtual generated column also gets added to row_size of the table
Posted by: Swaminatha Babu
Date: April 25, 2017 06:19AM

Hi,
I am using MySQL version '5.7.11-log'

for a specific project requirement I am trying to create virtual generated columns in a innodb table.
one of the innodb tables here requires some thing like 1000+ number of virtual generated columns.
since virtual generated column is virtual and does not use any storage, I thought only persisted columns would get accounted for row size limit and hence thought we can go upto innodb hard limit of 1017 columns per table.
However looks like this is not the case
depending upon the virtual generated columns datatype, the number of virtual columns I can create varies.

for example:
the table contains following 5 persisting columns
create table mytesttable (
id bigint auto_increment not null primary key,
entityid bigint not null,
recObj text null,
createDate datetime not null,
lastUpdateDate datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
thru alter table I am adding virtual generated columns slicing the data available in recObj like
alter table add column gencol1 varchar(10) as (substr(recObj,1,10) virtual

like this I am trying to create multiple virtual columns and db does not allow more than 824 columns

when the datatype of virtual generated column is changed to varchar(255) then the number of columns added reduces to 69

so does this mean virtual column size also gets added to row_size and hence this limits number of columns that can be added.

is this behavior expected?
as mentioned my thinking was that since the column is virtual this would not get added to row_size and hence can go upto hard limit of 1017

please clarify and help me understand virtual generated column

thanks for your help in advance


regards
Babu

Options: ReplyQuote


Subject
Views
Written By
Posted
Is virtual generated column also gets added to row_size of the table
119
April 25, 2017 06:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.