Re: Select Binary Field when UTF8 and Collate
We have a database that has one particular table, searchdata, that has
the
following columns
BINARY key_low(5)
BINARY key_high(5)
VARCHAR searchss(255)
The following query, when the SET QUERY UTF8 option is present,
doesn't work
because MySQL encodes the binary field.
Any ideas how we get around this?
We're running 4.1.5a gamma and the table encoding is set to utf8.
SELECT searchsss FROM searchdata WHERE '$D6@/' BETWEEN key_low AND
key_high;
I'm not sure I understand the problem. By SET QUERY utf8 do you mean
SET NAMES utf8? I couldn't find SET QUERY in the manual. The
following seems to work correctly to me:
SET NAMES utf8;
CREATE TABLE t (key_low tinyblob, key_high tinyblob, searchss
VARCHAR(255));
INSERT INTO t VALUES ('aaa', 'ccc', 'str1'),('AAA', 'CCC', 'str2');
SELECT searchss FROM t WHERE 'bbb' BETWEEN key_low AND key_high;
+--+
| searchss |
+--+
| str1 |
+--+
1 row in set (0.00 sec)
Isn't this what you want? A binary comparison? To get a
case-insensitive comparison use the following:
SELECT searchss FROM t WHERE 'bbb' COLLATE utf8_unicode_ci BETWEEN
key_low AND key_high;
+--+
| searchss |
+--+
| str1 |
| str2 |
+--+
2 rows in set (0.00 sec)
Or is there more that I don't understand?
best regards,
Jeremy
Kevin Cowley
RD
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different default collation in each connection
Ok, I thought that I will be misunderstood... I'll try to explain this in example below: I have table CREATE TABLE `product_descriptions` ( `id_product_description` mediumint(8) unsigned NOT NULL auto_increment, `id_product` mediumint(8) unsigned NOT NULL default '0', `lang` varchar(2) default NULL, `text_data` text, PRIMARY KEY (`id_product_description`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 in column `text_data` i have all texts in all languages (pl, en, ru etc.) So the problem is that this column is meant to store every collation for this language but in UTF-8. Now, when I get records from this table I should give database some info that I will use i.e. 'utf_8_polish_ci' collation on whole page - I don't need to change it on whole page (I use PHP by the way) This solution I use is very handy as when I need to add some language to my pages then I only add some info in application about what language this would be and... this should work. I can't change database structure on every language upgrade. Besides I would have for example 20 columns with different collations like: text_data_pl text_data_ru text_data_ro text_data_en text_data_... and this table is not the only one which stores information in different languages... So please consider this and please if you know tell me what collation_connection is for. -- Use the force - read the source Piotr Duszynski mailto:[EMAIL PROTECTED] Ok, now I think I understand better... from mysql manual: collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, it does not matter because columns have a higher collation precedence. http://dev.mysql.com/doc/mysql/en/Charset-connection.html If I understand this correctly this means that the _only_ time collation_connection is used is for an SQL statement like this: SELECT col1 FROM table1 WHERE stringliteral1 = stringliteral2; This isn't a very useful SQL query but it is legal and the only way to know how to compare the two string literals is to have a variable called collation_connection. When you are comparing, for example, a string literal to a column the collation of the column has a higher precedence than the collation_connection variable which is used for the string. Therefore the _only_ way to compare a string against a column without using the column's default collation is to explicitly specify which collation to use in the comparison with a COLLATE clause like this COLLATE utf8_polish_ci. It might be a little more work but you'll probably have to dynamically add a COLLATE clause to all your queries based on the language you want. best regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:RE: Indexing problem with UTF8 in 4.1.4?
Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. If it is converting to utf16 internally then the key would be 328*2+5+4 which is not 1000 bytes. If you only use the ascii range of utf8 then you are right that you are only _storing_ 1 byte per character. Mysql, however, must still _reserve_ 3 bytes for each character because other languages require more bytes per character (up to six but I believe mysql only supports 3 bytes for utf8 characters right now). So using your count of 343 * 3 = 1029. The index prefix lengths, by the way, are in characters not bytes. best regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different default collation in each connection
P.S2 I store data in one table which has no default collation set only UTF-8 charset. In this table I can have data in different collations. Every table does have a default collation either you specify it or it comes from the default collation for your database or the server default. You need to either: 1. set your table to use utf8_polish_ci or if you need more than one collation in your table 2. set the collation for the Polish columns to utf8_polish_ci. Otherwise you are probably using the default collation for utf8 which is either utf8_general_ci or utf8_unicode_ci I forget which. Collation _can_ be set for individual columns and it sounds like that is what you need to do. This is also important because each column index is collation specific and you need your columns to be indexed for the right collation. best regards, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 columns and MySQL
Hello, I have a table with the following column among others: text VARCHAR(120) CHARACTER SET utf8 The provider I am using however only offers MySQL version 3.23.58, so I cannot use this statement. At first I thought I was not going to be able to store UTF8. So I changed the column to the following: text VARCHAR(120) However, when I submit data from a web interface, much to my surprise, I was able to store some Chinese characters, which no doubt take up two bytes in UTF8. Great!!! Can anyone explain why this is and what exactly is going on under the hood? With the earlier versions you are able to store utf8 data but you cannot take advantage of case-insensitive character comparisons or correct sort order etc. You should probably change the Chinese columns from VARCHAR to one of the BLOB types so the data is treated as binary. Just curious, Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestions? Need a foreign key - but there are tradeoffs
I have two tables that should be related by a customer id (established in the customer table). The second table is going to have documents in a text column. I want the ability (at least I think) to have full text search for this second table. Hence I chose a myIsam table type. So a few things: 1-Can I realte the customer ID somehow to the customer table. In my application I'm passing the ID value into the record insertion. By the way , the customer table is set currently as Innodb. 2- I'm wondering just how bad it would be to not have full text search. I'm a bit unclear, but I suspect that I'd have to format the docs in a certain way to highlight the important keywords that would need to be searched for. 3-If I set both tables to myIsam or leave it as it is, is there another way to relate the id's together ? Yes, the ids are still related in the same way as they would be in an innodb table--the relationship just isn't _enforced_ by the DBMS. You have to enforce the relationship in the application logic. This is a very normal situation for everyone who uses myisam tables. If necessary you can simulate transactions with LOCK TABLES. So to insert a row into the child table which references a row in the parent table you could do something approximately like this: 1. use lock tables to lock both the parent and child tables--this is so that no one can delete the valid parent row before you are able to insert the child row 2. select the desired parent row from the parent table to make sure it exists 3. insert the child row which references the parent row 4. unlock tables Do something similar for updates and deletes Locking the tables like this can slow the application down if you have many concurrent updates/inserts/deletes, but not necessarily as much as one might think. The queries executed between the lock and unlock are usually very quick so the tables are not locked for very long and remember that with myisam tables the whole table is locked anyway for all update/deletes and sometimes for inserts so all you are doing is locking two tables at the same time instead of just one. It all depends on your application and the ratio of updates/inserts/deletes to selects as to whether this will work for you. best, Jeremy March Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 collations in 4.1.3
Entering it in hex works for me too. So the problem _was_ actually with the values I inserted into the database. What's the best way to actually see what is stored in the database, preferably as hex or something else that a terminal is guaranteed to display correctly? Clearly, what I was doing earlier was not correct. SELECT hex(your_column) FROM your_table; I usually convert utf8 to ucs2 so that I can recognize the codepoints easier. SELECT hex(CONVERT(your_column USING ucs2)) FROM your_table; There is also a new UNHEX() function which appeared in 4.1.2. best, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: UTF8 collations in 4.1.3
...
mysql SELECT col2 FROM test ORDER BY col2 COLLATE utf8_swedish_ci;
++
| col2 |
++
| M(u-diaresis)ller |
| Muffler|
| MX Systems |
| MySQL |
++
...
I expect M(u-diaeresis)ller to sort after MX Systems in the following:
...
I have tried various UTF8 collations and, apart from utf8_bin, they all
place M(u-diaresis)ller at the start.
...
Is this for Swedish language data? I don't know Swedish so I don't
actually know where u-diaeresis is sorted in Swedish myself, but
according to the source code (in the file: strings/ctype-uca.c) the
u-diaeresis is sorted as an equivalent of y in utf8_swedish_ci.
The unicode codepoint for u-diaeresis is 0x00FC and the capital
U-diaeresis is 0x00DC.
I just tested this with 4.1.4 (from the bk tree) and it worked correctly
for me. My keyboard isn't setup to enter u-diaeresis easily so I
entered it in hex. Try this:
CREATE TABLE swedish (col char(20) COLLATE utf8_swedish_ci);
INSERT INTO swedish VALUES (CONVERT(_ucs2 0x004D00FC006C006C00650072
USING utf8)), ('MySQL'), ('Muffler'), ('MX Systems');
SELECT * FROM swedish ORDER BY col;
++
| col|
++
| Muffler|
| MX Systems |
| Mller|
| MySQL |
++
4 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Unicode help
Could someone who has experience with handling the Unicode character set in a MySQL database please write to me? In particular, I am trying to discover how in an ASCII-limited environment one can specify non-ASCII characters; I do know their Unicode encodings, just not how to write an INSERT command in SQL that uses them. When using the mysql command line client I insert utf8 data like this: INSERT INTO your_table VALUES (CONVERT(_ucs2 0x0061 USING utf8)); if you prefer ucs2 then you don't need to convert: INSERT INTO your_table VALUES (_ucs2 0x0061); 0061 is the unicode codepoint for lowercase a. Just replace this with the codepoint(s) of the string you want to insert. You can also, of course, insert utf8 directly: _utf8 0x61, but who knows the utf8 codes beyond the basic latin characters?! If this doesn't fully answer your question just let me know, and I'll try to help more. Jeremy THANKS! Lisa N. Michaud, Assistant Professor of Computer Science Department of Mathematics and Computer Science, Wheaton College [EMAIL PROTECTED] http://cs.wheatoncollege.edu/lmichaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is the utf8_general_ci collation in sources?
Much has changed with Unicode in MySQL 4.1.3. So you should be sure to look at the newest bitkeeper source. And be sure to look at strings/ctype-uca.c. This is the new implementation of the Unicode Collation Algorithm. best, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
You know, now I'm sure that the chars are getting stored as '?' as well. I tried the test you suggested again, but with a small modification. I typed: SELECT IF(networkname='?', 1, 0) from networktable; and it returned 1. Because I used '?' instead of the chinese char and it matched, then obviously the stored character is a '?'. So my conclusion is the same as James Huang's; the problem happens when the string is stored. But no solution yet, though. The first thing to be sure to do is execute this query from the client: SET CHARACTER SET utf8; The best way to see what is actually being stored is to select the hex value of the column: SELECT HEX(your_column) FROM your_table; To see hex values as unicode codepoints convert the utf8 to ucs2: SELECT HEX(CONVERT(your_column USING ucs2)) FROM your_table; I'm not sure if this is equivalent to the example given in java before or not, but this is how I always insert hex values directly: INSERT INTO your_table VALUES (CONVERT(_ucs2 0x1234 USING utf8)); where '1234' is a unicode codepoint. This way you can enter the character as the codepoint and convert it to the utf8 equivalent. FYI I'm fairly sure that MyODBC will not support unicode until version 3.52. When I tried to use MyODBC for unicode a while back all I got was ???. You might try upgrading to 4.1.2--it has better support for character set conversions and a new ucs2_general_uca collation which uses the Unicode Collation Algorithm. Another thing to consider is that MySQL only supports utf8 characters up to 3 bytes long. I don't know if this is the case for chinese or not, but if so that might be another reason to use ucs2. good luck, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: german umlaut desperation (a requiem in utf-8)
now the really interesting part is: all other (or at least a lot of other) accented european characters are being displayed correctly _only_ german umlauts are allways shown as question marks. does anybody care to explain and give me a hint on what to do. i am completely confused. The first thing you should do is query your database to determine what actual hex value mysql is storing for the umlaut. SELECT HEX(CONVERT(german_col USING ucs2)) FROM your_table; This will show you the unicode code points for the data in german_col. I don't know off hand which unicode code points represent the German umlauts, but if you don't know you can look them up on unicode.org. If the code point being stored is the correct one for a German umlaut then you are having a display problem. This is probably due to an improper font, or the client or server not using the correct encoding. Don't forget SET CHARACTER SET utf8; on your client! If the the hex values you find in place of the umlaut are not correct then somehow your data is being corrupted/translated before it is loaded/inserted. Try inserting the hex value directly and see if MySQL stores this properly. INSERT INTO your_table VALUES (CONVERT(_ucs2 0x USING utf8)); where is the code point for the umlaut character. Hopefully this query will show the correct code point next to a correctly displayed umlaut character: SELECT HEX(CONVERT(german_col USING ucs2)), german_col FROM your_table; Good luck, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: How to get configuration info
Is there a way (utility, command, etc...) to find out what config settings a mysql server was started with? For example, someone started a server and I'd like to check to make sure the correct .cnf file was used? Thanks. David e.g. SHOW VARIABLES LIKE 'key_buffer_size%'; etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Using Indian languages and working with them using C API()
Can somebody give me pointers how to store and retrieve data in Hindi using MySQL C API and MySQL in general. Since alll the hindi text will be in unicode format and C API uses char* parameter how do I interface between my Hindi text data and C API. DO I convert them to some other format like utf-8 etc. I don't have very much experience writing clients in C, but I have been following the MySQL unicode development. If you haven't already it might help to look at the MySQL source that deals with unicode. In the strings directory there are two files ctype-utf8.c and ctype-ucs2.c. All the sorting and string comparisons for unicode are defined there. I think the conversion from char* to unicode is done there. I suggest you look at the latest source for 4.1.2 in the bitkeeper tree because changes are still being made to this code. I expect there will be more significant changes in the next couple of weeks. It might also help to read the SET CHARACTER SET and SET NAMES commands here: http://www.mysql.com/doc/en/Charset.html Good luck, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Basic Setup Questions!!!!
Have you tried: GRANT privileges ON database.* TO 'jupiter'@'192.168.6.%' IDENTIFIED BY 'sunrise'; See: http://www.mysql.com/doc/en/GRANT.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 - left function
Problem tested using both version 4.0.18 and 4.1.1 This query gets first byte of utf8 character, not two bytes If I use SELECT LEFT(FieldName, 2) AS FirstLetter, COUNT(*) FROM TableName GROUP BY FirstLetter gets right utf8 characters but others wrong for example; ++---+ | Ai | 1 | | Ak | 9 | | Ar |33 | || 8 | I tested with 4.1.1 and 4.1.2 and it works fine. Which version did you use with the test you provided? That is the expected behavior if you were using MySQL 4.0.18 with a single-byte character set--4.0.18 does not support utf8. If that is what you got with 4.1.1 then it is using the wrong character set somewhere. With 4.1.1 do: SHOW FULL COLUMNS FROM your_table; And be sure the collation field says utf8_general_ci. If it doesn't, then change the charset for your table: ALTER TABLE your_table CHARACTER SET utf8; Now try again. Also be sure your client is using utf8 with this command: SET CHARACTER SET utf8; I couldn't find 4.1.2 dev source for testing. Can you tell me how I can get it please. Does anybody knows another solution except getting dev source? I think this should work with 4.1.1, but here's how to get the dev source: Read here about installing development sources for 4.1.x: http://www.mysql.com/doc/en/Installing_source_tree.html This is the command you will enter to clone the source: shell bk clone bk://mysql.bkbits.net/mysql-4.1 mysql-4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: utf8 - left function
Hi, I need this query; SELECT LEFT(FieldName, 1) AS FirstLetter, COUNT(*) FROM TableName GROUP BY FirstLetter my datas in utf8 format. I can't get first characters of the datas in utf8 format but others is ok. Any suggestion? Thank you It works for me. I'm using the development source 4.1.2. Maybe the problem has already been fixed. You are using a 4.1.x version, right? It definitely would not work before 4.1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Union Query Sorting
Can anyone tell me how to sort the combined results of a Union query? (Select Name From Employee Group By Name Order By Name) Union (Select Name From Consultant Group By Name Order By Name); Just add another order by on the end after the parenthesis: (Select Name From Employee Group By Name Order By Name) Union (Select Name From Consultant Group By Name Order By Name) ORDER BY Name; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update field conditionally
UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. It looks like you just have the syntax wrong. Try: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), NULL); See: http://www.mysql.com/doc/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COLLATION
But when I do a select for specifcly word like sao, I get all the three !!! Internally MySQL isn't distinguing the different words, assuming that = A, and O = . I don't think MySQL has a Portuguese collation yet. You could try one of the other collations to see if its compatible with Portuguese. For instance: SELECT word FROM dictionary WHERE word = 'SA0' COLLATE latin1_spanish_ci; To see the collations available for your character set (latin1) type: SHOW COLLATION LIKE 'latin1%'; You could also try latin1_bin, but it would not be case insensitive. If these solutions aren't satisfactory you could try to write a Portuguese collation. Its not very hard and you could submit it to MySQL to include in their official distribution. See the documentation here: http://www.mysql.com/doc/en/Adding_character_set.html and here: http://www.mysql.com/doc/en/Charset.html I've done some work on a complex character set. Mine isn't quite finished, but Portuguese would probably be a simple one (see the docs for the difference between simple and complex charsets). I've learned enough that I could probably help you out with the Portuguese one if you need it. Good luck, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: WG: [mysql.com #3839] UTF8 and MySQL 4.1
What client are you using to connect to the server? If its MyODBC 3.51 then that is your problem. It doesn't support unicode yet. If you are using the mysql command line client try select Hex(your_column) from your_table; to see if the proper hex values are being inserted into the table. It will be easier to help you if you give us more specific problems you are having. best, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: inserting huge string
What I am trying to do is to enter a genome sequence which has around
175 characters. the file size is about 1.5MB which is well within
the longblob size. I am using python to enter the sequence from a file
to the database and the while i am running the python program ,the
connection to the db is lost. So it takes in only around 103500
characters.
To determine if the problem is with the server you could try inserting
from the command line client:
INSERT INTO your_table (longblob_col) VALUES
(LOAD_FILE('/path/to/yourfile'));
I just tried this and I could only get it to work if the file was in the
mysql data directory and had world readable permissions, but I could
successfully insert 5 MB files into long blobs after I increased
max_allowed_packet.
If that works then the problem is probably with your python client.
best,
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: On database structure -- keys and speed
I didn't key the placename because they're not guaranteed to be unique, but that means that a search for Artesia takes many (~30) seconds to complete, which is really not very useful. You should not use the placename as a PRIMARY KEY, but it can still have an index. You really should add a primary key to the placename table, though. To start I'd recomend the following: ALTER TABLE places ADD INDEX place_index (placename); ALTER TABLE places ADD COLUMN id int unsigned auto_increment primary key; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Mysqldump problem in output UTF-8 Char
in which table i set to use Charset (UTF-8). But When i use mysqlmysqldump -u myname -p mypwd telbookbook.txt, all string of table friend's c_name will become ?. Have you tried: mysqldump --default-character-set=utf8 -u myname -p mypwd telbookbook.txt If that doesn't work, which version of MySQL are you using? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with with-extra-charsets=none
I think --with-extra-charsets=none means that no additional character sets will be _compiled_ into the server. Complex character sets must be compiled into MySQL, but simple ones can be loaded dynamically. I think the character sets you are seeing are ones that can be loaded dynamically. For instance the big5 character set is probably not listed because it is one that has to be compiled in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Newbie Confused with Update involving Multiple Tables
http://www.mysql.com/doc/en/UPDATE.html I believe multi-table updates weren't supported until MySQL version 4.04. Are you using a version earlier than this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicating records
I tried this and doesn't work unforunately. I get ERROR: Duplicate entry... that occurs when copying from the temporary table back into the original table. Is there a way to do this, to copy a single record, without moving the primary key into the temporary table so I can avoid this error? Ari create temporary table t1 select ... from orig_table where ... insert into orig_table select ... from t1 Just insert null instead of the primary key: create temporary table t1 select ... from orig_table where ... insert into orig_table select null, your_column1, your_column2... from t1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with UTF-8 in 4.1.1a
It sounds like you are using the mysql client to display your queries. You should do: SELECT HEX(arabic_column) FROM your_table; to really see what characters are stored in the table. To see the unicode codepoints you can do this: SELECT HEX(CONVERT(arabic_column USING ucs2)) FROM your_table; If the mysql client only displays ? it could be that the terminal you are using does not have a proper unicode font. When you type arabic into the terminal is it displayed correctly? Another thing to try if you still can't input the data properly is to input the data as hex values: INSERT INTO your_table (arabic_column) VALUES (_utf8 0x); where is the utf8 hex value of the characters you want to enter. Since most people only know the ucs2 codepoint of the characters its usually easier to enter it like this: INSERT INTO your_table (arabic_column) VALUES (CONVERT(_ucs2 0x, USING utf8)); Finally just to be sure, type \s in the terminal to be sure the client and server are both using utf8. Hope this helps, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Character sets
but can I set multiple default character set options to multiple character sets (EUC-JP, EUC-KR, and ISO-8859-1) rather then just one. No, just one in 4.0.x. You have to use 4.1.x for multiple character sets. _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Query help
No. With the method you're using (storing the parent id with each record) you have to use a recursive series of queries to show the subtree levels. Take a look at this article--especially the second and third pages about the modified preorder tree traversal and nested sets. There are many other articles on this, but I found this one especially helpful starting out. It will let you do what you want, but this method has its drawbacks too. http://www.sitepoint.com/article/1105 _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL character set problem
The first things you should check: 1. Do \s from the mysql client to be sure its using the character set you told it to use with default-character-set=latin1. Be sure its the same one you used on your old server. 2. Since you have the Norwegian keyboard set up try typing Norwegian characters in the terminal. If they are displayed as expected then you know that the terminal font is not the problem. Having an improper terminal font is often the problem in cases like this. 3. Select the hex values for the characters which are not being displayed. SELECT hex(Norwegian_column) FROM your_table; If your old system is still available compare these values to the working system, just to be sure your data wasn't corrupted somewhere in the transfer. 4. Test with multiple clients if possible. Such as the command line client and a php web client. Hope this helps, Jeremy _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: auto sorting
Why don't you want to use an ORDER BY? I think its the _only_ way to get a stable order. It sounds like you want to retrieve your rows in the order in which they were entered. If you don't want an auto_increment field you could order by a timestamp. _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Certification Test Questions
In the Certification section of the mysql web site there is a short sample test which shows the types of questions. There's a lot of information there too including the broad topics covered in each test. According to amazon.com Paul Dubois will have a MySQL Certification study book coming out March 15th. I believe the test is geared toward the 4.x versions, but the web site should say for sure. _ High-speed usersbe more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Set SQL_MODE?!
Try this: mysql set @@session.sql_mode = 'ansi'; Query OK, 0 rows affected (0.00 sec) mysql select @@session.sql_mode; ++ | @@session.sql_mode | ++ | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI | ++ 1 row in set (0.00 sec) _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to determine user Privilege fields
Can I create a php script like:
No. Try this:
$privSQL = SHOW GRANTS FOR 'paul'@'localhost';;
$priv_result = mysql_query($privSQL) or die(Couldn't Select Priv);
while($myrow = mysql_fetch_row($priv_result))
{
echo $myrow[0]br;
}
I believe this only returns a single row with the grant statement used to
create the account, so you really don't need the loop at all, just this:
$myrow = mysql_fetch_row($priv_result);
echo $myrow[0]br;
_
Get reliable dial-up Internet access now with our limited-time introductory
offer. http://join.msn.com/?page=dept/dialup
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Japanese in MySQL
MySQL doesn't support unicode until version 4.1.0. If this isn't a production server I suggest you upgrade to MySQL 4.1.1. Its still in the ALPHA stage but it is pretty stable. I use it every day and I don't have any problems with it. Good luck, Jeremy _ Have fun customizing MSN Messenger learn how here! http://www.msnmessenger-download.com/tracking/reach_customize -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Japanese in MySQL
MySQL doesn't support unicode until version 4.1.0. If this isn't a production server I suggest you upgrade to MySQL 4.1.1. Its still in the ALPHA stage but it is pretty stable. I use it every day and I don't have any problems with it. Good luck, Jeremy _ Working moms: Find helpful tips here on managing kids, home, work and yourself. http://special.msn.com/msnbc/workingmom.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Unicode support in 4.0.17
Unicode and multiple character sets are not supported until version 4.10. See section 9 of the manual. 4.1.1 is still alpha but its quite stable if your server isn't in a production environment. _ Tired of slow downloads? Compare online deals from your local high-speed providers now. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Japanese in MySQL
I don't know what's wrong from your description, but here are some things to watch out for: Are BOTH the client and server using utf8? Is the client application using a unicode font that supports the characters you're using? Most fonts only support a subset of unicode characters. Are you using MySQL 4.1.1? Do a SHOW FULL COLUMNS FROM vocab; to be sure that the columns are using utf8. Try selecting the hex value of the columns to see if the values are being stored and transmitted properly: SELECT hex(japanese) FROM vocab; And remember that the utf8 hex value will not be the same as the unicode code points. To see the code points you can do this: SELECT hex(convert(japanese using ucs2)) FROM vocab; If you're testing in xterm with the mysql client be sure that it is set up for unicode and is using a proper font too. And do \s from the mysql client to see the server and client character sets being used. Good luck, Jeremy _ Get reliable dial-up Internet access now with our limited-time introductory offer. http://join.msn.com/?page=dept/dialup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Change from loop to single query
What language are you using? It's not clear from your example what language you're using (no $s, but you also forgot to increment your array so?), so I'll give you an example in PHP: $query_string = implode( OR name = , $yourarray); mysql_query(UPDATE users SET status = no WHERE name = $query_string;); _ Take advantage of our limited-time introductory offer for dial-up Internet access. http://join.msn.com/?page=dept/dialup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mySQL in Hebrew/my.cnf
You should check the error log about why the server didn't start. It should be called yourhostname.err in your data directory. I think the problem is that you put spaces around the second equal sign. The first equal sign set-variable = ... can be surrounded by spaces but the second one ... default-character-set=hebrew should not have any spaces. [mysqld] set-variable = default-character-set=hebrew You can also try invoking this option on the command line when starting the server like this: /usr/local/mysql/bin/mysqld_safe --default-character-set=hebrew with 3.23.xx I think the command is: /usr/local/mysql/bin/safe_mysqld --default-character-set=hebrew Again, no spaces around the equal sign. But putting it in my.cnf is probably the preferred method. Hope this helps, Jeremy
RE: mySQL in Hebrew
I don't think you can use alter table to change character sets in version 3.23.51. You must do it at the server level. Try starting mysql with this command line option: --default-character-set=hebrew Hope this helps. Jeremy
CASE...WHEN...THEN..., bug or not?
Using CASE... WHEN... THEN in my update query doesn't behave the way I expect it to. It's using the result of the query in the second WHEN condition rather than the original value. See this example: CREATE TABLE t1 (col_a tinyint not null, col_b tinyint not null); INSERT INTO t1 VALUES (1,1); UPDATE t1 SET col_a = CASE WHEN col_a = 1 THEN col_a + 1 ELSE 0 END, col_b = CASE WHEN col_a = 1 THEN col_b + 1 ELSE 0 END; mysql select * from t1; +---+---+ | col_a | col_b | +---+---+ | 2 | 0 | +---+---+ 1 row in set (0.00 sec) Shouldn't this return 2 in both columns? If this is the way CASE...WHEN...THEN... is supposed to behave, how can I make a query update two fields to one of two (or more) values depending on the value of one column without the result of one affecting the other. I can't use two update queries for the same reason. Thanks very much, Jeremy _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing field type
What command do I use to change the field type in my client_info table from: referral_percent | tinyint(4) | YES | NULL to referral_percent | double(3,2) unsigned zerofill | YES | NULL ALTER TABLE yourtable CHANGE referral_percent referral_percent double(3,2) unsigned zerofill default null; or: ALTER TABLE yourtable MODIFY referral_percent double(3,2) unsigned zerofill default null; See: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#ALTER_TABLE _ Send a QuickGreet with MSN Messenger http://www.msnmessenger-download.com/tracking/cdp_games -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Depth-first-traversal order?
I don't think you can do what you want in one query if you use the adjacency list tree model (i.e. a self join to the parent node). See the article below comparing the adjacency list and the modified preorder tree traversal: http://www.sitepoint.com/article/1105 I'm not sure if that is what you want or not, but its a good article and worth a shot. For my trees I use a combination of the adjacency list and the modified preorder tree traversal to get the best of both models. Another good reference that compares these two models is SQL for Smarties by Joe Celko. Hope this helps, Jeremy _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode with MySQL4.1
You need to be sure you are using a Unicode font. Are you using xterm? If so you can go to the edit tab and click Current Profile to change the font. Also note that most Unicode fonts only cover subsets of Unicode not the whole thing so use a font that supports the language you are using. I use the MiscFixed that comes with Red Hat and maybe other distributions too. It works for me: mysql select convert(_ucs2 0x00d4 using utf8); +--+ | convert(_ucs2 0x00d4 using utf8) | +--+ | Ô | +--+ 1 row in set (0.00 sec) Jeremy _ Is your computer infected with a virus? Find out with a FREE computer virus scan from McAfee. Take the FreeScan now! http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode with MySQL4.1
Oops--the Unicode character must have gotten lost in the email, but it does show up in MySQL. Its an O with a line above it. _ MSN Messenger with backgrounds, emoticons and more. http://www.msnmessenger-download.com/tracking/cdp_customize -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query for 25 records before and 25 records after a record alphabetically
I have an alphabetic list of records in a table and I want to display the 25 previous and 25 next records before and after a selected record. This would be easy if before and after meant sorted by id number, but I need it sorted alphabetically. Does anyone know a good way to do this? Thanks, Jeremy March _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode with MySQL4.1
Remember that utf8 is not the same as the Unicode codepoints. If you want to enter utf8 like that you need to convert it from ucs2 first. Try this: SELECT convert(_ucs2 0x00d4 using utf8); and to insert: INSERT INTO your_table values (convert(_ucs2 0x00d4 using utf8)); You can compare the character with its utf8 hexidecimal: SELECT hex(my_column), my_column FROM your_table; Hope this helps, Jeremy March _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case insensitive LIKE with utf8 (multibyte)
I'm working with the Greek unicode range on mysql 4.1, redhat 8.0. The problem is that querying with a LIKE expression is treated case and accent sensitive. The sort order and comparing with = do work correctly (case and accent insensitive). I noticed in the source code that the utf8 cs uses the simple LIKE function--could that be the problem? LIKE works correctly (case insensitive) for the Latin unicode range, I assume because it isn't multibyte. Has anyone else experienced this with multibyte utf8? Thanks, Jeremy March
