Appearance of the word BLOB in phpMyAdmin

[HowdeeDoodee]

After running the following code to create a table and then after inserting records, I am seeing the word BLOB in phpMyAdmin using Browse instead of the text in the fields.

 

Here is the table created.

CREATE TABLE `thistable`.`search_test` (`id` INT(11) NOT NULL, `name` TEXT NOT NULL, `description` TEXT NOT NULL, `price` INT(11) NOT NULL, `img` TEXT NOT NULL) ENGINE = MyISAM CHARACTER SET latin1 COLLATE latin1_bin;

 

Here are the records inserted.

 

INSERT INTO `search_test` (`id`, `name`, `description`, `price`, `img`) VALUES
(1, 'Nike running shoe', 'Fantastic nike running shoe. Comes in both blue and white', 100, 'nike.png'),
(2, 'Reebok basket shoe', 'Great basket shoe from Reebok. Unisex model. Comes in both red and white', 90, 'reebok.png'),
(3, 'Nike basket shoe', 'Basket shoe from Nike. Comes in both white and red', 80, 'nike2.png'),
(4, 'Reebok running shoe', 'Running shoe from Reebok. Comes in both white and red', 75, 'reebok2.png'),
(5, 'New Nike basket shoe', 'New Basket shoe from Nike. Comes in both blue and red', 80, 'nike3.png');

 

 

Here are the values appearing in the Browse option in phpMyAdmin.

 

 

      Edit  Delete  1  [bLOB - 17B] [bLOB - 57B] 100  [bLOB - 8B]

Edit Delete 2 [bLOB - 18B] [bLOB - 72B] 90 [bLOB - 10B]

Edit Delete 3 [bLOB - 16B] [bLOB - 50B] 80 [bLOB - 9B]

Edit Delete 4 [bLOB - 19B] [bLOB - 53B] 75 [bLOB - 11B]

Edit Delete 5 [bLOB - 20B] [bLOB - 53B] 80 [bLOB - 9B]

[nofaruccio]

Hi HowdeeDoodee,

I've your same problem...

Do you have finded a solution?

Do you have finded some bad repercussion?

Thank you for your collaboration.

Regards

Onofrio

[Zoombat]

Hi,

 

For indexes on BLOB and TEXT you need to specify a index prefix length.

So:

CREATE TABLE `thistable`.`search_test` (`id` INT(11) NOT NULL, `name` TEXT(255) NOT NULL, `description` TEXT(255) NOT NULL, `price` INT(11) NOT NULL, `img` TEXT(255) NOT NULL) ENGINE = MyISAM CHARACTER SET latin1 COLLATE latin1_bin;

 

For CHAR and VARCHAR, a prefix length is optional.

 

Also TEXT and BLOB fields are stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd

want to benchmark a realworld scenario with your data

 

Read about string types here: http://dev.mysql.com...e-overview.html

Edited December 12, 2012 by Zoombat

[Zoombat]

I also saw something wrong in your collation:

CHARACTER SET latin1 COLLATE latin1_bin;

 

A collation determines how the relational operators (<, >, etc.) and ORDER BY clauses sort strings. Issues considered by collations are:

• Are uppercase and lowercase letters considered equivalent?
  
• Is whitespace significant?
  
• Do accented letters sort equal to the unaccented versions, after the unaccented versions, or at the end?
  
• Are digraphs like "ch" and "ll" sorted like separate letters?
  
• Are Unicode compatibility equivalents like AᴬⒶＡ�������������������������� treated the same?
  

Reference manual MySQL says:

Specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared.

 

You'd better be aware that when you use latin1_bin, your TEXT becomes a BLOB.

bin > binary

You'd use latin1_general_ci or latin1_general_cs, this depends on your choise.

ci > case insensitive

cs > case sensitive

 

http://en.wikipedia....ry_large_object

http://www.joelonsoftware.com/articles/Unicode.html

Edited December 12, 2012 by Zoombat