我要评论环境：
- window10
- vs2022
- .net core 3.1
- mysql 8.0.25
- DBeaver
1. 整数类型
mysql支持5中整数类型，它们占用空间及表示范围如下所示：
一般我们建表时如下：
create table test( t_tinyint tinyint, t_smallint smallint, t_int int, t_mediumint mediumint, t_bigint bigint, t_tinyint_unsigned tinyint unsigned, t_smallint_unsigned smallint unsigned, t_mediumint_unsigned mediumint unsigned, t_int_unsigned int unsigned, t_bigint_unsigned bigint unsigned ) 使用sql测试的代码如下：
create table test( t_tinyint tinyint, t_smallint smallint, t_mediumint mediumint, t_int int, t_bigint bigint, t_tinyint_unsigned tinyint unsigned, t_smallint_unsigned smallint unsigned, t_mediumint_unsigned mediumint unsigned, t_int_unsigned int unsigned, t_bigint_unsigned bigint unsigned ) -- 查看元数据 select c.TABLE_SCHEMA ,c.TABLE_NAME ,c.COLUMN_NAME ,c.ORDINAL_POSITION,c.DATA_TYPE,c.NUMERIC_PRECISION ,c.COLUMN_TYPE from information_schema.`COLUMNS` c where TABLE_SCHEMA ='test' and TABLE_NAME ='test' order by ORDINAL_POSITION -- 插入数据 insert into test.test(t_tinyint,t_smallint,t_mediumint,t_int,t_bigint,t_tinyint_unsigned,t_smallint_unsigned,t_mediumint_unsigned,t_int_unsigned,t_bigint_unsigned) values (1,2,3,4,5,1,2,3,4,5),(-1,-2,-3,-4,-5,1,2,3,4,5); select * from test.test t
输出如下：
2. c#中的使用方式
在c#中它们的使用方式
-
tinyint
占用1个byte，和c#中的byte、sbyte正好一一对应。 -
smallint
占用2个byte，和c#中的short、ushort正好一一对应。 -
mediumint
占用3个byte，在c#中没有类型与之一一对象，可以使用int、uint代替。 -
int
占用4个byte，和c#中的int、uint正好一一对应。 -
bigint
占用8个byte，和c#中的long、ulong正好一一对应
上面表的模型可定义如下：
public class Model { /// <summary> /// column: tinyint /// </summary> public sbyte? t_tinyint { get; set; } /// <summary> /// column: smallint /// </summary> public short? t_smallint { get; set; } /// <summary> /// column: mediumint /// </summary> public int? t_mediumint { get; set; } /// <summary> /// column: int /// </summary> public int? t_int { get; set; } /// <summary> /// column: bigint /// </summary> public long? t_bigint { get; set; } /// <summary> /// column: tinyint unsigned /// </summary> public byte? t_tinyint_unsigned { get; set; } /// <summary> /// column: smallint unsigned /// </summary> public ushort? t_smallint_unsigned { get; set; } /// <summary> /// column: mediumint unsigned /// </summary> public uint? t_mediumint_unsigned { get; set; } /// <summary> /// column: int unsigned /// </summary> public uint? t_int_unsigned { get; set; } /// <summary> /// column: bigint unsigned /// </summary> public ulong? t_bigint_unsigned { get; set; } } 3. 其他
在mysql中，
-
SERIAL是bigint unsigned NOT NULL AUTO_INCREMENT的别名； -
bool是tinyint的同义词，0被认为是false，其他值认为是true； -
INTEGER是int的同义词；
可以使用create table test2( t_serial SERIAL)，执行后，mysql会自动将其识别为：CREATE TABLE test2 ( t_serial bigint unsigned NOT NULL auto_increment unique)
如下：
create table test2( t_serial SERIAL,t_bool bool,t_integer integer) -- 查看元数据 select c.TABLE_SCHEMA ,c.TABLE_NAME ,c.COLUMN_NAME ,c.ORDINAL_POSITION,c.DATA_TYPE,c.NUMERIC_PRECISION ,c.COLUMN_TYPE from information_schema.`COLUMNS` c where TABLE_SCHEMA ='test' and TABLE_NAME ='test2' order by ORDINAL_POSITION --查看定义sql show create table test.test2
发表评论