MySQL 数据类型

1：整形

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test (id TINYINT, idu TINYINT UNSIGNED, i INT UNSIGNED)");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,-98),
                ado.GetParameter("idu", DbType.UInt16,140),
                ado.GetParameter("i", DbType.UInt16,20),
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test VALUES (?id, ?idu, ?i)", param1);

            IDataParameter[] param2 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,"0"),
                ado.GetParameter("idu", DbType.UInt16,"0"),
                ado.GetParameter("i", DbType.UInt16,"0"),
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test VALUES (?id, ?idu, ?i)", param2);

            using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test"))
            {
                Assert.IsTrue(reader.Read());
                Assert.AreEqual(-98, (sbyte)reader.GetByte(0));
                Assert.AreEqual(140, reader.GetByte(1));
                Assert.IsTrue(reader.GetBoolean(1));
                Assert.AreEqual(20, reader.GetInt32(2));
                Assert.AreEqual(20, reader.GetInt32(2));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(0, reader.GetByte(0));
                Assert.AreEqual(0, reader.GetByte(1));
                Assert.IsFalse(reader.GetBoolean(1));

                Assert.IsFalse(reader.Read());
            }

2：小数

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            //ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test3 (fl FLOAT, db DOUBLE, dec1 DECIMAL(5,2))");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("fl", DbType.Double,2.3),
                ado.GetParameter("db", DbType.Double,4.6),
                ado.GetParameter("dec", DbType.Decimal,23.82),
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test3 VALUES (?fl, ?db, ?dec)", param1);

            IDataParameter[] param2 = new IDataParameter[]{
                ado.GetParameter("fl", DbType.Double,1.5),
                ado.GetParameter("db", DbType.Double,47.85),
                ado.GetParameter("dec", DbType.Decimal,123.85),
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test3 VALUES (?fl, ?db, ?dec)", param2);
            //将Double定义为int，数值会被四舍五入
            //Double和Decimal在范围允许的情况下可以互换

            using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test3"))
            {
                Assert.IsTrue(reader.Read());
                Assert.AreEqual(2.3, (decimal)reader.GetFloat(0));
                Assert.AreEqual(4.6, reader.GetDouble(1));
                Assert.AreEqual(23.82, reader.GetDecimal(2));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(1.5, (decimal)reader.GetFloat(0));
                Assert.AreEqual(47.85, reader.GetDouble(1));
                Assert.AreEqual(123.85, reader.GetDecimal(2));
            }

3：时间

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            //ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test4 (id INT NOT NULL, name VARCHAR(100), d DATE, dt DATETIME, tm TIME,  PRIMARY KEY(id))");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,1),
                ado.GetParameter("tm", DbType.String,"00:00"),
                
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param1);

            IDataParameter[] param2 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,2),
                ado.GetParameter("tm", DbType.String,"512:45:17"),
                
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param2);

            IDataParameter[] param3 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,3),
                ado.GetParameter("tm", DbType.String,"-07:24:00"),
                
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param3);

            //TimeSpan在用驱动的时候一定要选择String而不是Time。对于读取没有影响。
            //时间可谓负

            using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test4"))
            {
                reader.Read();

                object value = reader["tm"];
                Assert.AreEqual(value.GetType(), typeof(TimeSpan));
                TimeSpan ts = (TimeSpan)reader["tm"];
                Assert.AreEqual(0, ts.Hours);
                Assert.AreEqual(0, ts.Minutes);
                Assert.AreEqual(0, ts.Seconds);

                reader.Read();
                value = reader["tm"];
                Assert.AreEqual(value.GetType(), typeof(TimeSpan));
                ts = (TimeSpan)reader["tm"];
                Assert.AreEqual(21, ts.Days);
                Assert.AreEqual(8, ts.Hours);
                Assert.AreEqual(45, ts.Minutes);
                Assert.AreEqual(17, ts.Seconds);

                reader.Read();
                value = reader["tm"];
                Assert.AreEqual(value.GetType(), typeof(TimeSpan));
                ts = (TimeSpan)reader["tm"];
                Assert.AreEqual(-7, ts.Hours);
                Assert.AreEqual(-24, ts.Minutes);
                Assert.AreEqual(0, ts.Seconds);
            }

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test6");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test6 (id int, dt DATETIME, ts TIMESTAMP)");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
        //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
            DateTime time = DateTime.Now;
            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("id", DbType.Int16,1),
                ado.GetParameter("dt", DbType.String,time),
                ado.GetParameter("ts", DbType.String,time),
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt,ts) VALUES (?id, ?dt,?ts)", param1);
            //time,datetime,timespan类型可以为string

            using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test6"))
            {
                DataTable dt = ds.Tables[0];
                DateTime now = (DateTime)dt.Rows[0]["dt"];
                Assert.AreEqual(1, dt.Rows[0]["id"]);

                DateTime ts2 = (DateTime)dt.Rows[0]["ts"];
                Assert.AreEqual(now.Year, ts2.Year);

            }

4：bit

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test7");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test7 (bt1 BIT(2), bt4 BIT(4), bt11 BIT(11), bt23 BIT(23), bt32 BIT(32)) engine=myisam");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
            //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
            
            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("bt1", DbType.Int16,2),
                ado.GetParameter("bt4", DbType.Int16,3),
                ado.GetParameter("bt11", DbType.Int16,120),
                ado.GetParameter("bt23", DbType.Int16,240),
                ado.GetParameter("bt32", DbType.Int16,1000)
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test7 VALUES (?bt1, ?bt4, ?bt11, ?bt23, ?bt32)", param1);
            //BIT类型可为Null
            IDataParameter[] param2 = new IDataParameter[]{
                ado.GetParameter("bt1", DbType.Int16,null),
                ado.GetParameter("bt4", DbType.Int16,null),
                ado.GetParameter("bt11", DbType.Int16,120),
                ado.GetParameter("bt23", DbType.Int16,null),
                ado.GetParameter("bt32", DbType.Int16,null)
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test7 VALUES (?bt1, ?bt4, ?bt11, ?bt23, ?bt32)", param2);

            using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test7"))
            {
                Assert.IsTrue(reader.Read());
                Assert.AreEqual(2, reader.GetInt32(0));
                Assert.AreEqual(3, reader.GetInt32(1));
                Assert.AreEqual(120, reader.GetInt32(2));

                Assert.AreEqual(240, reader.GetInt32(3));
                Assert.AreEqual(1000, reader.GetInt32(4));


                Assert.IsTrue(reader.Read());
                Assert.IsTrue(reader.IsDBNull(0));
                Assert.IsTrue(reader.IsDBNull(1));
                Assert.AreEqual(120, reader.GetInt32(2));
                Assert.IsTrue(reader.IsDBNull(3));
                Assert.IsTrue(reader.IsDBNull(4));

            }

5：binary

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test8");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test8 (id INT, g BINARY(16), c VARBINARY(16), c1 BINARY(255))");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
            //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");

            Guid g = Guid.NewGuid();
            byte[] bytes = g.ToByteArray();

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("g",bytes),
                ado.GetParameter("c", bytes),
                ado.GetParameter("c1", g.ToString())
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test8 VALUES (1, @g, @c, @c1)", param1);
            //字节数组传递过程中是以Object类型传递的,BINARY(16)可为空
            using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test8"))
            {
                DataTable dt = ds.Tables[0];
                Assert.IsTrue(dt.Rows[0][1] is Guid);
                Assert.IsTrue(dt.Rows[0][2] is byte[]);
                Assert.IsTrue(dt.Rows[0][3] is byte[]);

                Assert.AreEqual(g, dt.Rows[0][1]);

            }

7：char

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test9");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test9 (id INT, name CHAR(12) CHARSET utf8)");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
            //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("name","文学")
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test9 VALUES (1, ?name)", param1);

            using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test9"))
            {
                reader.Read();
                string s = reader.GetString(1);
                Assert.AreEqual("Name", s);

            }

8：LongBlob

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test10");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test10 (id INT NOT NULL, blob1 LONGBLOB, PRIMARY KEY(id))");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
            //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
                         
            int lenIn = 1000000;
            byte[] dataIn = CreateBlob(lenIn);

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("id",1),
                ado.GetParameter("b1",dataIn)
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test10 VALUES (?id, ?b1)", param1);
            //LONGBLOB最大存储100万字节
            using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test10"))
            {
                byte[] dataOut = ds.Tables[0].Rows[0]["blob1"] as byte[];
                for (int x = 0; x < dataIn.Length; x++)
                    Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x);
            }

9：LongText

            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test11");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test11 (id INT NOT NULL, text1 LONGTEXT, PRIMARY KEY(id))");
            //ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
            //"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");

            int num = 25000;
            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < num; i++)
                sb.Append("我我我我我我我我我我");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("id",1),
                ado.GetParameter("text1",sb.ToString())
            };
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test11 VALUES (?id, ?text1)", param1);
            //LONGTEXT最大存储25万个汉字
            using (IDataReader dr = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test11"))
            {
                dr.Read();
                string aa = dr.GetString(1);
            }

10：总结

1：没有发现数组类型

2：大对象容量大，可以存储25万汉字