Skip to content

How to Limit Returned Text Size in Queries by MySQL Functions

Of course, you could choose to manipulate the returned query results in your application, but you could receive lots of unnecessary text in the round trip between the application and the database. Therefore, I suggest you to remove unwanted text in the database before reaching your application.

Let's do a simple math. Suppose you have a column that can store 4,000 bytes of character, your web page shows 20 records and cut the first 100 characters as the summary per records. The query result could carry up to 80 KB from the database to your application before truncating any unwanted text. That's why I suggest you to do everything the database can do before sending the result to the web server.

Now, you have two choices to get a part of text, either substr() or left() to truncate the trailing text of a column.

substr()
you have to indicate the starting position and the end position in this function, for example.
mysql> select id, title, substr(description, 1, 100) body from posts G
*************************** 1. row ***************************
   id: 1
title: How to Recover Truncated Tables in Oracle
 body: Truncate Table can destroys all data of a table without footprints, the result is a kind of dropping
*************************** 2. row ***************************
   id: 2
title: How to Password Protect Web Directories by HTDIGEST
 body: HTTP Basic Authentication - htpasswd is a quite old technology which has been lasted and evolved for
2 rows in set (0.00 sec)

A shorter result set.
mysql> select id, title, substr(description, 1, 10) body from posts G
*************************** 1. row ***************************
   id: 1
title: How to Recover Truncated Tables in Oracle
 body: Truncate T
*************************** 2. row ***************************
   id: 2
title: How to Password Protect Web Directories by HTDIGEST
 body: HTTP Basic
2 rows in set (0.00 sec)


left()
you have to indicate the returned length in this function, for example.
mysql> select id, title, left(description, 100) body from posts G
*************************** 1. row ***************************
   id: 1
title: How to Recover Truncated Tables in Oracle
 body: Truncate Table can destroys all data of a table without footprints, the r
esult is a kind of dropping
*************************** 2. row ***************************
   id: 2
title: How to Password Protect Web Directories by HTDIGEST
 body: HTTP Basic Authentication - htpasswd is a quite old technology which has
been lasted and evolved for
2 rows in set (0.00 sec)

A shorter result set.
mysql> select id, title, left(description, 10) body from posts G
*************************** 1. row ***************************
   id: 1
title: How to Recover Truncated Tables in Oracle
 body: Truncate T
*************************** 2. row ***************************
   id: 2
title: How to Password Protect Web Directories by HTDIGEST
 body: HTTP Basic
2 rows in set (0.00 sec)

In my opinion, left() is more convenient than substr(), but substr() can do more complicate operations.

Leave a Reply

Your email address will not be published. Required fields are marked *