Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

#1071 - Specified key was too long; max key length is 767 bytes
                Because its not 520 bytes, but rather, 2080  bytes, which far exceeds 767 bytes, you could do column1 varchar(20) and column2 varchar(170).  if you want a character/byte equiv, use latin1
– Rahly
                Dec 18, 2015 at 0:21
                i think your calculation is a bit wrong here. mysql uses 1 or 2 extra bytes to record the values length: 1 byte if the column's max length is 255 bytes or less, 2 if it's longer than 255 bytes. the utf8_general_ci encoding needs 3 bytes per character so varchar(20) uses 61 bytes, varchar(500) uses 1502 bytes in total 1563 bytes
– lackovic10
                Nov 7, 2016 at 12:35
                mysql> select maxlen, character_set_name from information_schema.character_sets where character_set_name in('latin1', 'utf8', 'utf8mb4');  maxlen | character_set_name ------ | ------------------- 1      | latin1 ------ | ------------------- 3      | utf8 ------ | ------------------- 4      | utf8mb4
– lackovic10
                Nov 7, 2016 at 13:02
                'if you want a character/byte equiv, use latin1' Please don't do this. Latin1 really, really sucks. You will regret this.
– Stijn de Witt
                Dec 5, 2016 at 10:07
767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).
You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 255 (or minus null-terminator, 254 characters).
One option you have is to just place lower limit on your VARCHAR fields.
Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:
ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );
Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements possible, which would allow you to implement the intended business rules without hitting the MySQL limitation.
                To apply by specifying a subset of the column rather than the entire amount. A good solution.
– Steven
                Nov 29, 2009 at 4:14
                This doesn't explain why fields well below the length limit are exceeding the length limit...
– Cerin
                Nov 8, 2013 at 17:15
                I've tried editing in the information @Cerin is missing above, which clearly considered missing by others as well, but it gets rejected as being more suitable as a comment. For those trying to understand why 500 + 20 > 767 see Stefan Endrullis' comment on Julien's answer.
– Letharion
                Jan 14, 2015 at 8:19
                This can be a problem. For example: I have field name (255) and add unique to this field at name(191) as I'm using utf8mb4. If I have my user add their name with 'IJUE3ump5fiUuCi16jSofYS234MLschW4wsIktKiBrTPOTKBK6Vteh5pNuz1tKjy...aO500mlJs' And the other user add their name with this 'IJUE3ump5fiUuCi16jSofYS234MLschW4wsIktKiBrTPOTKBK6Vteh5pNuz1tKjy...aO500mlJa' The different is the last character. It should pass validation not stuck at duplicate entry.
– vee
                Jul 30, 2016 at 15:24
                The index limit is 767 bytes, not characters. And since Mysql's utf8mb4 character set (which the rest of the world calls utf8) needs (at most) 4 bytes per character you can only index up to VARCHAR(191). Mysql's utf8 character set (which the rest of the world calls broken) needs at most 3 bytes per character so if you are using that (which you shouldn't), you can index up to VARCHAR(255)
– Stijn de Witt
                Dec 5, 2016 at 10:17
                Yes, specifying ENGINE=InnoDB DEFAULT CHARSET=utf8 at the end of the CREATE TABLE statement I was able to have a VARCHAR(255) primary key. Thanks.
– xonya
                Jun 10, 2018 at 14:07
                Indeed! useful for some languages like Vietnamese which we must use utf8mb4 for the accuracy in search!
– Alex
                Jan 11, 2019 at 9:19
                Following this answer, I searched all the VARCHAR(255) in my sql   dump file and replaced them by VARCHAR(191) . This worked. I have no idea about possible sideeffect I used it to import data from a server to localhost.
– Kaizoku Gambare
                Jul 20, 2019 at 9:26
                The number of allowed characters just depends on your character set. UTF8 may use up to 3 bytes per character, utf8mb4 up to 4 bytes, and latin1 only 1 byte.  Thus for utf8 your key length is limited to 255 characters, since 3*255 = 765 < 767.
– Stefan Endrullis
                Jul 23, 2014 at 8:25
                AS Stefan Endrullis stated, it depends on the charset. If you use UTF8 which uses 3 bytes: 255x3=765 which is lower than the 767 limit, while 256x3=768 which is higher. But if you use UTF8mb4 its 255*4=1020, so its not a realy solution
– bernhardh
                Jun 2, 2016 at 9:22
                This answer is correct. However if 255 is indeed working for you, it means you are using Mysql utf8, which unfortunately is broken. It can only encode characters in the basic multilingual plane. You will get issues with characters falling outside of that. For example those Emoji characters they have been adding fall outside of it I think. So instead of switching to VARCHAR(255), switch to VARCHAR(191) and switch the encoding to utf8mb4 (which is actually just utf8, but MySql wanted to keep back. compat).
– Stijn de Witt
                Dec 5, 2016 at 10:28
                Not helpful for my multicolumn unique constraint. It also would not work for the OP's multicolumn unique constraint. (would give it a total size of 825 bytes)
– Barett
                Mar 2, 2017 at 16:53
MySQL assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.
So assuming you're using 'utf8', your first column will take 60 bytes of the index, and your second another 1500.
                — Which presumably means that when using utf8mb4, I need to set them to (at most) 191  as 191*4 = 764 < 767.
– Isaac
                Feb 25, 2016 at 14:25
                I think this might be the right answer, but could you elaborate on what one would need to do to correct such an issue? At least for MySQL noobs like me?
– Adam Grant
                Jun 3, 2016 at 0:09
                There's no one way to get around this index limit. The question here is about unique constraints; for those, you can have one column of unlimited length text, and another where you store a hash (like MD5) of that text, and use the hash column for your unique constraint. You'll have to ensure your program keeps the hashes up-to-date when it alters the text, but there's various ways to handle that without too much trouble. Quite frankly, MySQL should implement such a thing itself so you don't have to; I wouldn't be surprised if MariaDB has something like that built-in.
– morganwahl
                Jun 4, 2016 at 22:51
                A unique index on a very long varchar column is rare. Think about why you need it because it might be a design issue. If you just want an index on it for search, consider some 'keywords' field which fits within 191 characters, or split text into short description and long/complete text etc. Or if you really need full text search, consider using specialized software for it, such as Apache Lucene.
– Stijn de Witt
                Dec 5, 2016 at 10:34
Solution For Laravel Framework
As per Laravel 5.4.* documentation; You have to set the default string length inside the boot method of the app/Providers/AppServiceProvider.php file as follows:
use Illuminate\Support\Facades\Schema;
public function boot() 
    Schema::defaultStringLength(191); 
Explanation of this fix, given by Laravel 5.4.* documentation:
  
Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider.
  
Alternatively, you may enable the innodb_large_prefix option for your
  database. Refer to your database's documentation for instructions on
  how to properly enable this option.
                I've done some research and found different views. This problem arises because of the use of indexing "unique" in the email field in the users table. So simply adding a limit value of 250 can solve the problem. This value is added to the file migration which creates the users table on the line with the code "unique ()" so it looks like this: $table->string('email', 250)->unique ();
– Danang Ponorogo
                Aug 16, 2020 at 5:02
                Is there any downside to changing to innodb_large_prefix globally? And is that DB "global" or all DBs TOTALLY GLOBAL?
– SciPhi
                Jun 5, 2014 at 22:02
                Only applies when using non-standard row formats. See dev.mysql.com/doc/refman/5.5/en/…. Specifically, 'Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.' The default row format is unaffected.
– Chris Lear
                Jul 15, 2014 at 11:19
                This worked well for me - more details and a guide can be found here: mechanics.flite.com/blog/2014/07/29/…
– cwd
                Feb 24, 2015 at 2:59
                Important missing details from this answer. innodb_file_format must be BARRACUDA and At the table level you have to use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See @cwd's comment above with the guide.
– q0rban
                May 9, 2018 at 15:59
                If it's UTF8, a character can use up to 4 bytes, so that 20 character column is 20 * 4 + 1 bytes, and the 500 char column is 500 * 4 + 2 bytes
– Thanatos
                Nov 29, 2009 at 3:46
                For what it's worth, i just had the same problem and switching from utf8_general_ci to utf8_unicode_ci solved the problem for me. I do not know why though :(
– Andresch Serj
                Jul 17, 2012 at 7:24
                For a VARCHAR(256) column with a UNIQUE index, changing collation had no effect for me, like it did for @Andresch. However, reducing the length from 256 to 255 did solve it. I don't understand why, as 767 / max 4 bytes per character would yield a maximum of 191?
– Arjan
                Nov 5, 2012 at 14:45
                255*3 = 765; 256*3 = 768. It appears your server was asssuming 3 bytes per character, @Arjan
– Amber
                Nov 5, 2012 at 16:11
                @Greg: You are correct, but this should be elaborated: UTF-8 itself uses 1–4 bytes per code point. MySQL's "character sets" (really encodings) has a character set called "utf8" that is able to encode some of UTF-8, and uses 1–3 bytes per code point, and is incapable of encoding code points outside the BMP. It also includes another character set called "utf8mb4", which uses 1–4 bytes per code point, and is capable of encoding all Unicode code points. (utf8mb4 is UTF-8, utf8 is a weird version of UTF-8.)
– Thanatos
                May 8, 2013 at 22:54
                Why exactly should one do that? Additionally, if this has any downsides (which I assume), you should mention this
– Nico Haase
                May 11, 2020 at 13:22
                This will mean your column won't be able store some Unicode characters; most notable emojis.
– Martin Tournoij
                Jan 19, 2021 at 12:07
                I believe you do not want to make this change. But if you do actually want this, in MySQL 5.6+ you should be using utf8mb3, not utf8. The latter is just an alias to the former, and the alias is planned to remap in the future. When using old/deprecated standards, denote that explicitly so you don't get a surprise "upgrade". Encouraged by 5.6, 5.7, and 8.0 docs.
– AndrewF
                Mar 9 at 16:23
  
I think varchar(20) only requires 21 bytes while varchar(500) only
  requires 501 bytes. So the total bytes are 522, less than 767. So why
  did I get the error message?




    

UTF8 requires 3 bytes per character to store the string, so in your case 20 + 500 characters = 20*3+500*3 = 1560 bytes which is more than allowed 767 bytes.
The limit for UTF8 is 767/3 = 255 characters, for UTF8mb4 which uses 4 bytes per character it is 767/4 = 191 characters.
There are two solutions to this problem if you need to use longer column than the limit:
Use "cheaper" encoding (the one that requires less bytes per character)

In my case, I needed to add Unique index on column containing SEO string of article, as I use only [A-z0-9\-] characters for SEO, I used latin1_general_ci which uses only one byte per character and so column can have 767 bytes length. 
Create hash from your column and use unique index only on that

The other option for me was to create another column which would store hash of SEO, this column would have UNIQUE key to ensure SEO values are unique. I would also add KEY index to original SEO column to speed up look up.
The answer about why you get error message was already answered by many users here. My answer is about how to fix and use it as it be.
Refer from this link.
Open MySQL client (or MariaDB client). It is a command line tool.
It will ask your password, enter your correct password.
Select your database by using this command use my_database_name; 
  
Database changed
set global innodb_large_prefix=on;
  
Query OK, 0 rows affected (0.00 sec)
set global innodb_file_format=Barracuda;
  
Query OK, 0 rows affected (0.02 sec)
Go to your database on phpMyAdmin or something like that for easy management. > Select database > View table structure > Go to Operations tab. > Change ROW_FORMAT to DYNAMIC and save changes.
Go to table's structure tab > Click on Unique button.
Done. Now it should has no errors.
The problem of this fix is if you export db to another server (for example from localhost to real host) and you cannot use MySQL command line in that server. You cannot make it work there.
                if you are still error after enter above query, try to go to "phpmyadmin" > set the "collation" to your preference (for me i use "utf8_general_ci") > click apply (even if its already utf8)
– Anthony Kal
                May 25, 2017 at 13:41
                I'm not using a tool that works with your instructions, but I'm still voting it up for trying to help people with actually fixing the problem. There are endless explanations of what causes the problem, but remarkably little on how to actually solve it.
– Teekin
                Jun 19, 2019 at 16:19
You got that message because 1 byte equals 1 character only if you use the latin-1 character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
5 workarounds:
The limit was raised in 5.7.7 (MariaDB 10.2.2?). And it can be increased with some work in 5.6 (10.1).
If you are hitting the limit because of trying to use CHARACTER SET utf8mb4. Then do one of the following (each has a drawback) to avoid the error:
⚈  Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
⚈  Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈  Use a "prefix" index -- you lose some of the performance benefits.
⚈  Or... Stay with older version but perform 4 steps to raise the limit to 3072 bytes:
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)
-- http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
                Note that this will not allow you to do range scans over these columns. Prefix lengths on VARCHARs will allow you to keep this trait, while causing possibly spurious matches in the index (and scanning and row lookup to eliminate them) (see the accepted answer). (This is essentially a manually implemented hash index, which sadly MysQL doesn't support with InnoDB tables.)
– Thanatos
                May 8, 2013 at 22:58
                I couldn't use prefix indices because I needed to maintain compatibility with H2 for testing purposes, and found using a hash column works well. I would strongly recommend using a collision resistant function such as SHA1 instead of MD5 to prevent malicious users from creating collisions. An index collision could be exploited to leak data if one of your queries only checks the hash value, and not the full column value.
– Mike Rippon
                May 21, 2020 at 19:30
Go to App\Providers\AppServiceProvider.php.
Add this to provider   use Illuminate\Support\Facades\Schema; in top.
Inside the Boot function Add this Schema::defaultStringLength(191);
that all, Enjoy.
                This is a bad solution. Reason: indexes are not meant to be infinitely-long. When you apply a unique index to something, you want the index to have fixed-with most of the time. That means you SHOULDN'T make stuff like email unique, but you should hash the email and make that one unique. Unlike raw string-data, hashes are fixed width and can be indexed and made unique without issues. Instead of understanding the problem, you're spreading awful practices that don't scale.
– N.B.
                Aug 21, 2019 at 22:25
We encountered this issue when trying to add a UNIQUE index to a VARCHAR(255) field using utf8mb4. While the problem is outlined well here already, I wanted to add some practical advice for how we figured this out and solved it.
When using utf8mb4, characters count as 4 bytes, whereas under utf8, they could as 3 bytes. InnoDB databases have a limit that indexes can only contain 767 bytes. So when using utf8, you can store 255 characters (767/3 = 255), but using utf8mb4, you can only store 191 characters (767/4 = 191).
You're absolutely able to add regular indexes for VARCHAR(255) fields using utf8mb4, but what happens is the index size is truncated at 191 characters automatically - like unique_key here:
This is fine, because regular indexes are just used to help MySQL search through your data more quickly. The whole field doesn't need to be indexed.
So, why does MySQL truncate the index automatically for regular indexes, but throw an explicit error when trying to do it for unique indexes? Well, for MySQL to be able to figure out if the value being inserted or updated already exists, it needs to actually index the whole value and not just part of it.
At the end of the day, if you want to have a unique index on a field, the entire contents of the field must fit into the index. For utf8mb4, this means reducing your VARCHAR field lengths to 191 characters or less. If you don't need utf8mb4 for that table or field, you can drop it back to utf8 and be able to keep your 255 length fields.
                This is what worked for me too. I had a varchar(250), but the data was never that long. Changed it to varchar(100). Thanks for the idea :)
– Arun Basil Lal
                Jun 8, 2019 at 10:08
  
I just drop database and recreate like this, and the error is gone:
  
drop database if exists rhodes; create database rhodes default
  CHARACTER set utf8   default COLLATE utf8_general_ci;
However, it doesn't work for all the cases. 
It is actually a problem of using indexes on VARCHAR columns with the character set utf8 (or  utf8mb4),  with VARCHAR columns that have more than a certain length of  characters. In the case of utf8mb4, that certain length is 191.
Please refer to the Long Index section in this article for more information how to use long indexes in MySQL database: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4 
                I confirm that my issue was the database collation too. I don't have any explanation for it. I use mysql v5.6.32 and DB collation was utf8mb4_unicode_ci.
– mahyard
                Jun 25, 2019 at 9:05
I did some search on this topic finally got some custom change
For MySQL workbench 6.3.7 Version Graphical inter phase is available
Start Workbench and select the connection.
Go to management or Instance and select Options File.
If Workbench ask you permission to read configuration file and then allow it by pressing OK two times.
At center place Administrator options file window comes.
Go To InnoDB tab and check the innodb_large_prefix if it not checked in the General section.
set innodb_default_row_format option value to DYNAMIC.
For Versions below 6.3.7 direct options are not available so need to go with command prompt
Start CMD as administrator.
Go To director where mysql server is install Most of cases its at
"C:\Program Files\MySQL\MySQL Server 5.7\bin" so command is 
"cd \"
"cd Program Files\MySQL\MySQL Server 5.7\bin".
Now Run command
mysql -u userName -p databasescheema
Now it asked for password of respective user.
Provide password and enter into mysql prompt.
We have to set some global settings enter the below commands one by one
set global innodb_large_prefix=on;
set global innodb_file_format=barracuda;
set global innodb_file_per_table=true;
Now at the last we have to alter the ROW_FORMAT of required table by default its COMPACT we have to set it to DYNAMIC.
use following command
alter table table_name ROW_FORMAT=DYNAMIC;
                if I use set global innodb_default_row_format = DYNAMIC; I see this message: ERROR 1193 (HY000): Unknown system variable 'innodb_default_row_format'
– Adrian Cid Almaguer
                Jan 24, 2017 at 14:58
Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider:
use Illuminate\Support\Facades\Schema;
 * Bootstrap any application services.
 * @return void
public function boot()
    Schema::defaultStringLength(191);
Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.
Reference from blog : https://www.scratchcode.io/specified-key-too-long-error-in-laravel/
Reference from Official laravel documentation : https://laravel.com/docs/5.7/migrations
Based on the column given below, those 2 variable string columns are using utf8_general_ci collation (utf8 charset is implied). 
In MySQL, utf8 charset uses a maximum of 3 bytes for each character. Thus, it would need to allocate 500*3=1500 bytes, which is much greater than the 767 bytes MySQL allows. That's why you are getting this 1071 error.
In other words, you need to calculate the character count based on the charset's byte representation as not every charset is a single byte representation (as you presumed.) I.E. utf8 in MySQL is uses at most 3-byte per character, 767/3≈255 characters, and for utf8mb4, an at most 4-byte representation, 767/4≈191 characters.
It's also known that MySQL
column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci
In my case, I had this problem when I was backing up a database using the linux redirection output/input characters. Therefore, I change the syntax as described below. PS: using a linux or mac terminal.
Backup (without the > redirect) 
# mysqldump -u root -p databasename -r bkp.sql
Restore (without the < redirect ) 
# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql
The error "Specified key was too long; max key length is 767 bytes" simple disappeared.
I found this query useful for detecting which columns had an index violating the max key length:
SELECT
  c.TABLE_NAME As TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS DataType,
  c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
  s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
  ON s.table_name = c.TABLE_NAME
 AND s.COLUMN_NAME = c.COLUMN_NAME 
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.CHARACTER_MAXIMUM_LENGTH > 191 
  AND c.DATA_TYPE IN ('char', 'varchar', 'text')
Due to prefix limitations this error will occur. 767 bytes is the stated prefix limitation for InnoDB tables in MySQL versions before 5.7 . It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.
Running the following on the service giving you the error should resolve your issue. This has to be run in the MYSQL CLI.
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;
There are max key length limits in MySQL.
InnoDB — max key length is 1,536 bytes (for 8kb page size) and 768 (for 4kb page size) (Source: Dev.MySQL.com).
MyISAM — max key length is 1,000 bytes (Source Dev.MySQL.com).
These are counted in bytes!  So, a UTF-8 character may take more than one byte to be stored into the key.
Therefore, you have only two immediate solutions:
Index only the first n'th characters of the text type.
Create a FULL TEXT search — Everything will be Searchable within the Text, in a fashion similar to ElasticSearch
Indexing the First N'th Characters of a Text Type
If you are creating a table, use the following syntax to index some field's first 255 characters: KEY sometextkey (SomeText(255)). Like so:
CREATE TABLE `MyTable` (
    `id` int(11) NOT NULL auto_increment,
    `SomeText` TEXT NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `sometextkey` (`SomeText`(255))
If you already have the table, then you can add a unique key to a field with: ADD UNIQUE(ConfigValue(20));.  Like so:
ALTER TABLE
MyTable
ADD UNIQUE(`ConfigValue`(20));
If the name of the field is not a reserved MySQL keyword, then the backticks (```) are not necessary around the fieldname.
Creating a FULL TEXT Search
A Full Text search will allow you to search the entirety of the value of your TEXT field.  It will do whole-word matching if you use NATURAL LANGUAGE MODE, or partial word matching if you use one of the other modes.  See more on the options for FullText here: Dev.MySQL.com
Create your table with the text, and add the Full text index...
ALTER TABLE
        MyTable
ADD FULLTEXT INDEX
        `SomeTextKey` (`SomeTextField` DESC);
Then search your table like so...
SELECT
        MyTable.id, MyTable.Title,
MATCH
        (MyTable.Text)
AGAINST
        ('foobar' IN NATURAL LANGUAGE MODE) AS score
        MyTable
HAVING
        score > 0
ORDER BY
        score DESC;