精华内容
下载资源
问答
  • Character collation

    2021-01-12 17:48:59
    collation is latin1_general_ci. At phpMA front page I choice MySQL connection collation as latin1_general_ci. If I want to export and save content to file, every french character in the file is messed...
  • Support collation

    2020-12-08 20:07:45
    <div><p><strong>Is your feature request related to a ...Support collation https://docs.mongodb.com/manual/reference/collation/</p><p>该提问来源于开源项目:aravindnc/mongoose-paginate-v2</p></div>
  • Database collation

    2021-01-09 00:56:01
    <div><p>Add the database collation in the config for more accurate sorting. <p>http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html</p><p>该提问来源于开源项目:anchorcms/anchor-cms...
  • SpellCheck collation

    2020-12-26 19:18:47
    <div><p>Mauricio, these were the changes I have made to get collation to work for spell check. I am running an older version with XMLNodes instead of XMLElements Since there was a change to XML Node ...
  • collation support

    2021-01-02 14:38:52
    <p>MongoDB supports the collation operation. For example, <p>db.zort.find({},{_id:0}).collation({locale: "en" }).sort( {name: 1} ); { "name" : "alice" } { "name" : "...
  • <ul><li>Entry for COLLATION FOR added to functions and operators doc page</li><li>New example for pg_collation_for and COLLATION FOR added to COLLATE page</li></ul>该提问来源于开源项目:...
  • MySQL collation方法

    2020-09-11 10:42:00
    在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。
  • Dynamic collation

    2020-11-28 21:39:11
    <div><p>Implements dynamic collation as requested and described in #1188. The user can declare a stream on a <code>DynamicMap</code> returning a (Nd)Layout, and then specify which item in the Layout ...
  • When trying to add any collation, an unknown exception is raised. If you insist on saving the model after the excption, then the loaded model will crash on any attempt to export/validate it. <p>...
  • collation doesnt change

    2021-01-12 16:35:32
    <div><p>Well i changed my db collation but although phpmyadmin sais that collation changed when i browse my tables the collation is still latin_swedish <p>example <p>cpg132_albums 12 MyISAM ...
  • 4.2.10. Collation Expressions 4.2.10.排序规则表达式 The COLLATE clause overrides the collation of an expression. It is appended to the expression itapplies to: COLLATE子句会重写表达式的排序规则。它...
    4.2.10. Collation Expressions
    4.2.10.排序规则表达式
    The COLLATE clause overrides the collation of an expression. It is appended to the expression it applies to:
    COLLATE子句会重写表达式的排序规则。它放到表达式后面:
     
    expr COLLATE collation
     
    where collation is a possibly schema-qualified identifier. The COLLATE clause binds tighter than operators; parentheses can be used when necessary.
    collation可能是一个有模式限定的标识符。COLLATE子句比运算符优先级更高;如有必要,可使用括号。
     
    If no collation is explicitly specified, the database system either derives a collation from the columns involved in the expression, or it defaults to the default collation of the database if no column is involved in the expression.
    如果未明确指定排序规则,则数据库系统要么从表达式中涉及的列中得出排序规则,要么在表达式中不涉及任何列时将其设置为数据库的默认排序规则。
     
    The two common uses of the COLLATE clause are overriding the sort order in an ORDER BY clause,for example:
    COLLATE子句的两种常见用法是覆盖ORDER BY子句中的排序顺序,例如:
     
    SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";
     
    and overriding the collation of a function or operator call that has locale-sensitive results, for example:
    并覆盖具有对语言环境敏感的函数或运算符调用时的排序规则,例如:
     
    SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
     
    Note that in the latter case the COLLATE clause is attached to an input argument of the operator we wish to affect. It doesn't matter which argument of the operator or function call the COLLATE clause is attached to, because the collation that is applied by the operator or function is derived by considering all arguments, and an explicit COLLATE clause will override the collations of all other arguments.(Attaching non-matching  COLLATE clauses to more than one argument, however, is an error. For more details see Section 23.2.) Thus, this gives the same result as the previous example:
    注意,在后一种情况下,COLLATE子句附加到我们希望影响的运算符的输入参数上。COLLATE子句附加到的运算符或函数的自变量无关紧要,因为运算符或函数调用时所应用的排序规则是针对所有变量的,而显式COLLATE子句将覆盖所有其他参数的排序规则。(但是,将不匹配的COLLATE子句附加到多个参数上是一个错误。有关更多详细信息,请参见第23.2节。)因此,以下得出的结果与前面的示例相同:
     
    SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';
     
    But this is an error:
    而下面,是一个错误的示例:
     
    SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";
     
    because it attempts to apply a collation to the result of the > operator, which is of the non-collatable data type boolean.
    因为上例试图将排序规则应用到操作符>上,而类型boolean并不是一个排序规则数据类型。
     
    查看表的排序规则:
     
    mydb=# \d dept
                           Table "public.dept"
    Column |          Type          | Collation | Nullable | Default
    --------+------------------------+-----------+----------+---------
    id     | numeric                |           |          |
    dept   | character varying(100) |           |          |
    name   | character varying(100) |           |          |
     
     
     
     
    mydb=# select table_schema,
    mydb-#        table_name,
    mydb-#        column_name,
    mydb-#        collation_name
    mydb-# from information_schema.columns
    mydb-# where collation_name is not null
    mydb-# order by table_schema,
    mydb-#          table_name,
    mydb-#          ordinal_position;
        table_schema    |              table_name               |          column_name           | collation_name
    --------------------+---------------------------------------+--------------------------------+----------------
    information_schema | _pg_foreign_data_wrappers             | fdwoptions                     | C
    information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_catalog   | C
    information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_name      | C
    information_schema | _pg_foreign_data_wrappers             | authorization_identifier       | C
    information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_language  | C
    查看数据库的排序规则:
     
    mydb=# select datname,
    mydb-#        datcollate
    mydb-# from pg_database;
      datname  | datcollate  
    -----------+-------------
    postgres  | en_US.UTF-8
    mydb      | en_US.UTF-8
    template1 | en_US.UTF-8
    template0 | en_US.UTF-8
    (4 rows)
     
    展开全文
  • Wrong table collation

    2020-12-01 15:15:53
    <p>Just one problem at the moment, the generator uses the collation <code>utf8mb4_unicode_ci</code> for every table. <p>This should be more dynamically cause each table can have an other collation....
  • Collation and charset

    2020-12-09 12:50:42
    should work and use</code>collation: 'UTF8MB4_GENERAL_CI'<code>as default. Also collations should be specified with</code>collation:, no</code>charset`. </p><p>该提问来源于开源项目:...
  • m using the standard <code>NOCASE</code> collation instead of our custom Unicode one, just to try to eliminate some variables. It still fails.) <p>queryAlbums generates the SQL query: <pre><code>sql ...
  • 在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。问题是这样的:一张test的表,字符集采用的latin1。select to_id from test where to_id='cn象_王';+----...

    在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。

    问题是这样的:

    一张test的表,字符集采用的latin1。

    select to_id from test where to_id='cn象_王';

    +---------------+

    | to_id |

    +---------------+

    | cn陶_陶 |

    | cn象_王 |

    +---------------+

    2 rows in set (0.00 sec)

    取cn象_王的数据,居然把cn陶_陶的数据也取回来了。

    这显然是不允许的。

    查看它们的编码:

    (root@im_offlog1a)[test]> select hex('cn陶_陶');

    +----------------+

    | hex('cn陶_陶') |

    +----------------+

    | 636ECCD55FCCD5 |

    +----------------+

    1 row in set (0.00 sec)

    (root@im_offlog1a)[test]> select hex('cn象_王');

    +----------------+

    | hex('cn象_王') |

    +----------------+

    | 636ECFF35FCDF5 |

    +----------------+

    1 row in set (0.00 sec)

    编码的确是不一样的,但是为什么mysql会认为这两条记录是一样的呢?

    一开始我们就把问题定位于collation引起的问题。

    show variables查看

    | collation_connection | latin1_swedish_ci

    | collation_database | latin1_swedish_ci

    | collation_server | latin1_swedish_ci

    手工把这些参数修改为latin1_bin,结果居然一样。这下感觉真是奇怪了。

    这里先解释一下mysql collation的命名规则:

    它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束

    比如latin1字符集有以下几种校正规则:

    校对规则 含义

    latin1_german1_ci 德国DIN-1

    latin1_swedish_ci 瑞典/芬兰

    latin1_danish_ci 丹麦/挪威

    latin1_german2_ci 德国 DIN-2

    latin1_bin 符合latin1编码的二进制

    latin1_general_ci 多种语言(西欧)

    latin1_general_cs 多种语言(西欧ISO),大小写敏感

    latin1_spanish_ci 现代西班牙

    最后我们将表格重建,手工指定表格级别的collation为latin1_bin。

    这个问题就得到了解决。

    那么问题又来了,为什么我前面手工测试latin1_bin时不生效呢?

    原来MySQL按照下面的方式选择表字符集和 校对规则:

    如果指定了CHARACTER SET X和COLLATE Y,那么采用CHARACTER SET X和COLLATE Y。

    如果指定了CHARACTER SET X而没有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。

    否则,采用服务器字符集和服务器校对规则。

    而我们在建表的时候指定了character set,所以它永远是采用对应的默认的校对规则。

    当然我们其实也没必要重建表格,只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin这样转换即可。

    另外建议collation都尽量采用字符集相应的bin类型的校对规则,这样不容易出错。

    再说说我自己的体会

    觉得 character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改进,只是新版的先用 character set 定字符集,再用此字符集名字加 _bin 定校对规则为二进制的,从而确保中文查询正确。

    再测试了一下,把此字段属性改为不带 BINARY 的

    ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 ) DEFAULT NULL

    然后再看表结构确实变成 `thread` varchar(50) default NULL, 即不带 character set latin1 collate latin1_bin 了,可见character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改进。

    此外还读到更方便的做法,不用逐个改字段属性,而只要表格级别的collation为latin1_bin就行了。

    测试:

    alter table comment_content_1_01 CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin

    后,

    再导出表结构

    CREATE TABLE comment_content_1_01 (

    content_id int(11) NOT NULL auto_increment,

    thread varchar(50) collate latin1_bin default NULL,

    uname varchar(100) collate latin1_bin default NULL,

    nick varchar(100) collate latin1_bin default NULL,

    uid int(11) unsigned default NULL,

    content text collate latin1_bin,

    post_time datetime default NULL,

    post_ip int(10) unsigned default NULL,

    `status` enum('unaudit','normal','deleted') collate latin1_bin NOT NULL default 'unaudit',

    PRIMARY KEY (content_id)

    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

    即便原来没定各字段的 collate,现在也全都是 collate latin1_bin 了。

    本文标题: MySQL collation方法

    本文地址: http://www.cppcns.com/shujuku/mysql/45175.html

    展开全文
  • Set database collation

    2020-12-09 02:42:01
    <div><p>Set database collation to match upstream settings. Related to https://github.com/gitlabhq/gitlabhq/pull/8195.</p><p>该提问来源于开源项目:gitlabhq/omnibus-gitlab</p></div>
  • Postgres Npgsql collation

    2021-01-05 04:18:57
    <div><p>Unfortunately, the Stackoverflow ... What is the recommendation about controlling the db collation on creation with npgsql? <p>Thanks!</p><p>该提问来源于开源项目:npgsql/npgsql</p></div>
  • show collation显示MySQL支持字符集的排序规则。如下:mysql>showcollation;+--------------------------+----------+-----+---------+----------+---------+|Collation|Charset|Id|Default|Compiled...

    show collation

    显示MySQL支持字符集的排序规则。如下:mysql> show collation;

    +--------------------------+----------+-----+---------+----------+---------+

    | Collation                | Charset  | Id  | Default | Compiled | Sortlen |

    +--------------------------+----------+-----+---------+----------+---------+

    | big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |

    | big5_bin                 | big5     |  84 |         | Yes      |       1 |

    ...

    +--------------------------+----------+-----+---------+----------+---------+

    222 rows in set (0.00 sec)

    还可以使用“like”过滤排序规则。如下:mysql> show collation like '%utf8_ge%';

    +--------------------------+---------+-----+---------+----------+---------+

    | Collation                | Charset | Id  | Default | Compiled | Sortlen |

    +--------------------------+---------+-----+---------+----------+---------+

    | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |

    | utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |

    | utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |

    +--------------------------+---------+-----+---------+----------+---------+

    3 rows in set (0.00 sec)

    展开全文
  • MySql:charset和collation的设置charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和链接级www.2cto.com1.服务器级查看设置:... 和 show global variables like 'collation_server';修改设置...

    MySql:charset和collation的设置

    charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和链接级

    www.2cto.com

    1.服务器级

    查看设置:show global variables like 'character_set_server'; 和 show global variables like 'collation_server';

    修改设置:在OPTION FILE (/etc/mysql/my.cnf)里设置:

    [mysqld]

    character_set_server=utf8

    collation_server=utf8_general_ci

    2. 数据库级

    查看设置:select * from information_schema.schemata where schema_name = 'cookbook';

    设置:

    1.若没有显式设置,则自动使用服务器级的配置

    2.显式设置:在建立库时指定

    create database playUtf8  DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

    www.2cto.com

    3.表级

    查看设置:show create table course;

    设置:

    1.若没有显式设置,则自动使用数据库级的配置

    2.显式设置:在建立表时指定

    create table utf ( id int ) default charset=utf8 default collate=utf8_bin;

    4.列级

    查看设置:show create table course;

    设置:

    1.若没有显式设置,则自动使用表级的配置

    2.显式设置:

    CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);

    5.链接级别

    查看设置:

    show variables like 'character_set_client';  # 服务端使用这个编码来理解客户端发来的statements

    show variables like 'character_set_connection' ; # 我还不知道什么意思,等看了mysql

    源码再说

    show variables like 'character_set_results'; # 服务端使用这个编码回送结果集和错误信息

    设置:

    客户端在链接时能够指定这些参数;同时,服务端也提供了一个Global范围的值,客户端未指定这些参数时,服务端就使用这个Global值。这个global值怎么设置的? 我查遍了不少文档,彷佛还没看到设置的办法 (有人说经过my.cnf,或者在启动

    mysqld时指定命令行参数,其实都是错的)

    附:connector/j传输SQL时用什么编码?

    答案: "The character encoding between client and server is automatically detected upon connection. The encoding used by the driver is specified on the server using the character_set_server system variable for server versions 4.1.0 and newer."

    也就是说,是在链接时查询服务器端的character_set_server值,再肯定链接将使用的编码。

    不过,官方文档还说,"要想覆盖客户端上的自动检测编码功能,可在用于链接到服务器的URL中使用“characterEncoding”属性。"

    展开全文
  • Remove collation options

    2021-01-12 18:13:45
    <ol><li>How do I edit the collation options set ? On my test server I only want a short set - how can I edit it? The list must be stored somewhere but I can't find it via any text search 2. 3...
  • New collation format

    2020-12-01 22:30:47
    <div><p>This implements the new collation format described in #420. <p>I deviated from the description slightly by removing the <code>period</code> field for now (see ...
  • 作者:VEPHP 时间 2017-09-15《MYSQL数据库MySQL collation方法》要点:本文介绍了MYSQL数据库MySQL collation方法,希望对您有用。如果有疑问,可以联系我们。问题是这样的:一张test的表,字符集采用的latin1....
  • mysql collation

    2017-02-08 10:55:59
    登录数据 mysql -uroot -p123456 -default-...mysql的collation大致的意思就是字符序。首先字符本来是不分大小的,那么对字符的>, = , utf8 的字符序: select * from collations; utf8编码默认的字符
  • Use default collation

    2020-12-09 01:07:21
    <p>utf8mb4_unicode_ci is the default database collation. I have a migration file including new tables with a normal setup: <pre><code>php $this->table('countries') ->addColumn('...
  • Collation feature of MongoDB

    2020-12-25 21:19:45
    <div><p>MongoDB has released the collation feature from 3.4 and meteor also supports it(with rawCollection). But, I cannot find a way to use collation in easy-search(Mongo Engine). </p><p>该提问来源于...
  • New Collation not compatible

    2020-12-01 12:20:53
    Message returned: ERROR: new collation (en_US.UTF8) is incompatible with the collation of the template database (en_US.UTF-8) HINT: Use the same collation as in the template database, or use template0...
  • Add collation to query

    2021-01-12 15:50:46
    } ).collation( { locale: 'en', strength: 2 } ) Is there anyway to add collation to query like above query?</p><p>该提问来源于开源项目:jenssegers/laravel-mongodb</p></div>
  • 经常发现同事爲了更改DB的Collation而删掉DB,重新创建过。其实,只要一条SQL就可以解,唔使哽麻烦!!
  • mysql默认字段值区分大小写:character-set-server=utf8collation-server=utf8_bininit-connect="SET NAMES utf8;"SELECT DEFAULT_CHARACTER_SET_NAME "charset", DEFAULT_COLLATION_NAME "collation"FROM ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,022
精华内容 2,408
关键字:

collation