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?
2
-
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...– jcho360Commented May 8, 2012 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?– qazwsxCommented May 8, 2012 at 20:17
Add a 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.
answered May 9, 2012 at 13:26
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
-
The problem with TEXT is that the field is also a primary key. It seems primary key can not be stored as TEXT.– qazwsxCommented May 8, 2012 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/…– jcho360Commented May 8, 2012 at 20:37
-
I tried it and I think you are right, just use varchar(length) to create text index.– jcho360Commented May 8, 2012 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.– db2Commented May 9, 2012 at 17:22