truncating mysql field

daarius

hi guys,
anyone know of how to drop the field size of a mysql field, on mysql side of things. e.g. if a field "description" in a table is of LONGTEXT and has tons of text in it. when i do query to get the value i just want the first 100 characters. i dont want to get the entire value transfered from mysql side and then do substr() on php side. but something on mysql server it self, tell the mysql before of the field size in bytes to use for resultsets?

any ideas? been looking in mysql system variables, but couldnt find anything.

thanks.

Plasma

Yep.

Look into the MySQL string functions ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html )

What your after is SUBSTR (or Substring).

SELECT SUBSTR(field, 100) AS shortfield FROM table

shortfield will only have the first 100 characters (or less if there is less data, obviously) from the 'field' column.

daarius

thanks.

but there isnt any way on server side as in configuring the system variables to output so many bytes by default to all the queries being passed?

Plasma

Not that I am aware of (why is it a problem?).

If you are wanting to limit the amount of text that can be stored in the field, give it a limit value in the column structure.

daarius

its a long story... but problem is the queries can not be changed. also, if the mysql is getting all the data out e.g. 20MB but user is only using first 100 chars. its quite bad if the whole thing is in a loop to get 100 records.

just like we have a system var of mysql "max_allowed_packet" to fiddle with, i was looking for something similar on field sizes in resultset.

in java api 1.5, under sql.Statement, we have a method Statement.setMaxFieldSize()
i am copying that behaviour. but dont want to do it on php side, but rather mysql.

laserlight

I think this cannot be done without changing the query. Consider that each column does have some size limitation, but this size limitation is imposed on storage, not retrieval. A size limitation on retrieval is already possible due to the flexibility offered by SQL, so it makes sense to tailor the SQL query to retrieve as much as needed rather than having a separate control to limit the size on retrieval.

What is the 'long story' that makes it such that you cannot change the query? Perhaps there is a way around that.

daarius

well, the story is that i am developing sql package for my class libraries which is exactly same API as java 1.5 so if someone been coding in java they will have no probs in using this library for db stuff. everything is done, i am just working on field size. the java doc for the method setMaxFieldSize() says that the rest of the data in the field is "silently dropped". this of course i can do on php side using substr(). but there is nothing i can do qith queries, as this is a class library.

but i was hoping something to be done on the mysql side of things.

there is also a java implementation of sql package by MySQL guys, a jdk driver. i have looked in that source, but they have just dummy implementation for this feature. ie. does nothing.

if interested you can try it here:
http://www.phpobject.com

laserlight

Ah, okay. I am not familiar with the Java API involved, but my guess is that you can emulate this by silently changing the query based on whether the function was invoked.