MySQL从库的列类型不一致导致的复制异常问题

官方文档：https://dev.MySQL.com/doc/refman/5.6/en/replication-features-differing-tables.html

创新互联专注于企业营销型网站建设、网站重做改版、拉萨网站定制设计、自适应品牌网站建设、H5响应式网站、成都做商城网站、集团公司官网建设、外贸营销网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为拉萨等各大城市提供网站开发制作服务。

slave_type_conversions 这个参数在mysql5.5.3引入，目的是启用row格式的bin-log的时候，如果主从的column的数据类型不一致，会导致复制失败，mysql5.5.3之后支持，主库是int从库是bigint这种类型的复制，

这个参数的意义就是控制些类型转换容错性。

如果从库的字段类型范围比主库类型大，那么设置slave_type_conversions=ALL_NON_LOSSY后复制没有问题的。

如果从库类型比主库类型小，比如从int复制到tinyint，虽然可以通过设置slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY让主从不出问题，但是实际上会造成数据丢失的风险。

几种值的设置：

ALL_LOSSY：允许数据截断

ALL_NON_LOSSY：不允许数据截断，如果从库类型大于主库类型，是可以复制的，反过了，就不行了，从库报复制错误，复制终止。

ALL_LOSSY,ALL_NON_LOSSY:所有允许的转换都会执行，而不管是不是数据丢失。

空值（不设置）：要求主从库的数据类型必须严格一致，否则都报错。

Mode	Effect
ALL_LOSSY	In this mode, type conversions that would mean loss of information are permitted. This does not imply that non-lossy conversions are permitted, merely that only cases requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the slave.
ALL_NON_LOSSY	This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.【确保从库的列类型更宽泛些也不会导致复制报错】 Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error.
ALL_LOSSY,ALL_NON_LOSSY	When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
ALL_SIGNED	Treat promoted integer types as signed values (the default behavior).
ALL_UNSIGNED	Treat promoted integer types as unsigned values.
ALL_SIGNED,ALL_UNSIGNED	Treat promoted integer types as signed if possible, otherwise as unsigned.
[empty]	When slave_type_conversions is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types. This mode is the default.