Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: collation (reset)

Collation options for new MySQL schemas and tables created in MySQL for Excel
Employee +0 Vote Up -0Vote Down

In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0, MySQL for Excel has always let you create new schemas and tables in a MySQL database, in versions lower than 1.3.0 these were created with the default character set and collation defined in the MySQL server. Starting with version 1.3.0 we introduced several features regarding collations:

  • New drop-downs were added that let you to override the default collation for new MySQL schemas and tables.
  • Default collations for each schema can be shownin the schemas list below the schema names.
  • The SQL queries for schemas creation can be previewed or displayed (depending on its global setting).

Remember you can install the latest

  [Read more...]
Adding a case insensitive, distinct unicode collation
+6 Vote Up -1Vote Down

Every once in a while questions like the one in MySQL Bug #60843 or Bug #19567 come up:

What collation should i use if i want case insensitive behavior but also want all accented letter to be treated as distinct to their base letters?

or shorter, as the reporter of bug #60843 put it:

I need something like utf8_bin + ci

utf8_general_ci and utf8_unicode_ci unfortunately do not provide this behavior and utf8_bin is obviously not case insensitive.

read more

How To – Configure MySQL to Use UTF-8
+1 Vote Up -1Vote Down

Background Knowledge


Using the character set UTF-8 allows for the use of any language, can represent every character in the Unicode character set and is backward compatibility with ASCII. Not to mention is can handle any platform and be sent through many different systems without corruption. With such advantages this is why so many are making the switch.

The following instructions were done on Debian Squeeze v6.04 AMD64 operating system using MySQL v14.14 Distrib 5.1.61.

Solution – Server Configuration


At present MySQL is configured by default to use “latin1″ character set. Here’s how to change MySQL configuration to use UTF-8 character set and collation.

  • Check MySQL’s current configuration, run the following two SQL statements.
    1
    2
    
    SHOW VARIABLES LIKE '%collation%'; 
    SHOW
  •   [Read more...]
    Understanding MySQL binary and non-binary string data types
    +0 Vote Up -0Vote Down
    Having reviewed different table structures designed by different people, I have come to the conclusion that binary and non-binary string data types are used without consideration of the consequences of choosing either one. The confusion stems from the fact that both non-binary and binary string data appear to store characters because they can be saved as quoted string.
    DBJ – MySQL Character Sets
    +0 Vote Up -0Vote Down

    In our latest article at Database Journal we talk about Character Sets in MySQL.  What are they?  How do they affect searching?  How do they affect data that is inserted or updated?  How can I set and control the for an application or globally in my database?  And what pre-tell is collation?  We answer all these questions and more.

    Database Journal – Understanding MySQL Character Sets

    Charset support in MySQL is really not all that complex
    +3 Vote Up -1Vote Down

    The headline is flame-bait, don’t take it. I just wanted to point something out about character sets and collations in MySQL.

    To the uninitiated, it may seem overwhelming. Everything has a character set! Everything has a collation! And they act weirdly! The server has one. The database has one (oh, and it changes magically as I USE different databases.) Every table has one, and columns too. Is that all? NO! My connection has one! Kill me now!

    Relax. In truth, only one kind of thing actually has a charset/collation. That is values. And values are stored in columns. The only thing that really has a charset/collation is a column.[1]

    What about all the rest of those things — connection, database, server, table? Those are just defaults, which determine what charset/collation a

      [Read more...]
    Running a case sensitive query in on a case insensitive table
    +0 Vote Up -0Vote Down

    A colleague at work asked me “how can I run a case sensitive select on a case insensitive table?” out of curiosity and for a moment I hesitated, then said, yeah why not :) ….

    Below are two different approaches (one of which is quite inefficient) and if anyone has another way, better or worse, please do leave a comment with your suggested approach :).

    Cheers,
    Darren

    Preparation


    mysql [localhost] {root} (test) > create table t1(a varchar(20));
    Query OK, 0 rows affected (0.02 sec)

    mysql [localhost] {root} (test) > insert into t1 (a) values ('darren');
    Query OK, 1 row affected (0.00 sec)

    mysql [localhost] {root} (test) > insert into t1 (a) values ('Darren');
    Query OK, 1 row affected (0.00 sec)

    mysql






      [Read more...]
    Unicode coming to PHP 6
    +0 Vote Up -0Vote Down
    The move from PHP 5 to PHP 6 will be a painful one. But once it’s done, I hope that it will be easier to handle safe web development for a global, multi-language internet. After all these years, we still … Continue reading →
    MySQL: Collation matters when using unique indexes
    +0 Vote Up -0Vote Down

    When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."

    Here is an example:
    The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

    PLAIN TEXT CODE:
  •  
  • CREATE TABLE text1 (

  •   [Read more...]
    Showing entries 1 to 9

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.