MySQL Forums
Forum List  »  General

Why MySQL substr returns value in data type Text when length is over 512?
Posted by: Sanjaya Balasuriya
Date: July 07, 2021 06:36PM

I've noticed that MySQL substr() returns value in data type text when the length is over 512.

Please check the example below;

Data table:

create table temp10( c0 int primary key auto_increment, c1 varchar(2500));
insert into temp10(c1) values ('abcd');
Secondary table using temp10 as source;

create table temp11 as
select c1,
substr(c1, 1, 10) as c2,
substr(c1, 1, 100) as c3,
substr(c1, 1, 512) as c4,
substr(c1, 1, 513) as c5,
substr(c1, 1, 2500) as c6
from temp10;
Structure of the table created is;

Table: temp11 Columns:

c1 varchar(2500)
c2 varchar(10)
c3 varchar(100)
c4 varchar(512)
c5 text
c6 text
Is this an expected/ documented behavior?

This same behavior has been noticed for scalar subqueries within select clause too.

Options: ReplyQuote


Subject
Written By
Posted
Why MySQL substr returns value in data type Text when length is over 512?
July 07, 2021 06:36PM


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.