Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL, how to manually edit values of a BLOB column in few tuples?

For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the first place?

share|improve this question
    
I'm not sure 100% what you mean, but BLOB is used a lot to store files, like images. how to edit that? I think if you make a select of that field the only think that you'll see is BINARY code, so probably edit it wont be easy... –  jcho360 May 8 '12 at 17:42
    
The field is used to store variable length text. I need to edit it in situ in the database using some tools. (If you have recommendations, please do; I'm thinking about phpmysqladmin) Should I use TEXT instead of BLOB to store that field? –  Problemania May 8 '12 at 20:17
add comment

2 Answers

When deciding to store the field as BLOB or TEXT, this paragraph might be helpful:

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set. [src]

The difference outlined here being in the way values are compared and sorted.

You can use the UPDATE statement to modify the values.

Now, from some of the comments, I garnered that this field is a primary key. If this table is InnoDB, and this table has secondary indexes (other indexes that are not primary key), and you expect the table to become very large, then be aware how InnoDB handles secondary indexes.

InnoDB will store the primary key along with the value of the secondary indexes, so your index will grow much larger than if you used an autoincremented int column as the primary key.

share|improve this answer
add comment

I would recommended varchar(length) or text (here it's blob vs text forum), but that is up to you. PHPmyadmin it's friendly, but you can modify text using the "update" statement too using console. If you are using windows I will recommend mysql administrator and if you are using mac then would be Mysql Workbench, because you have lot of tools besides to make a simple query

UPDATE: After make some search I found that you can make a text field like index but not as primary key, someone did the same question in SOF, basically the solution is to change the primary key from text to varchar, if you want to know more deeper why check this link

share|improve this answer
    
The problem with TEXT is that the field is also a primary key. It seems primary key can not be stored as TEXT. –  Problemania May 8 '12 at 20:34
    
change the storage engine, probably you are using innodb, with Myisam you can use text as primary key, please take a loot to this forum where someone have something like that stackoverflow.com/questions/10180762/… –  jcho360 May 8 '12 at 20:37
    
I tried it and I think you are right, just use varchar(length) to create text index. –  jcho360 May 8 '12 at 20:53
    
I'm going to go out on a limb and say that if you're trying to use a TEXT column as a primary key, you're probably doing something very wrong. –  db2 May 9 '12 at 17:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.